# Look up text based on two criterias

1. ## Look up text based on two criterias

Hello.

Ihave a data list containing these columns (See attached excel):

ToolNr, Machine number, Tool name
T1, 5011, Drill 10 mm
T1, 5012, Drill 12 mm
T1, 5013, Drill 11 mm

etc....

IŽd like to make a table with machine numbers and toolnumber like the one below.

5011 5012 5013 5014 5015 5016
T1 Drill 10 mm Drill 12 mm Drill 11 mm
T2
T3
T4
T5
T6
T7
T8

How can I write a function that looks upp the data list and completes the table with the Tool name for each tool number and machine?

/Anders

2. ## Re: Look up text based on two criterias

You need a bog standard INDEX MATCH MATCH:

=INDEX(\$R\$4:\$W\$11,MATCH(A4,\$Q\$4:\$Q\$11,0),MATCH(B4,\$R\$3:\$W\$3,0))

3. ## Re: Look up text based on two criterias

No, that dit not work...
I added your code in cell R4 and it did not find the tool name "Drill 10 mm"....
Any clues?

/Anders

4. ## Re: Look up text based on two criterias

Type in cell R4
=IFERROR(INDEX(\$C\$4:\$C\$8,MATCH(1,(\$A\$4:\$A\$8=\$Q4)*(\$B\$4:\$B\$8=R\$3),0)),"")
then hit Ctrl+Shift+Enter

5. ## Re: Look up text based on two criterias

Oh, I see ... Sorry!

Which version of Excel are you using?

6. ## Re: Look up text based on two criterias

Hi!!!

This worked! Very much thank you!!

7. ## Re: Look up text based on two criterias

I have office 365. Josephteh solved it!

8. ## Re: Look up text based on two criterias

Although we value your privacy as much you do, it could be important that members have a rough idea of your location as the solutions they offer may be affected by your locale. For instance, you might in the future post questions which are related to your regional settings.

With this in mind, please update your profile to something more precise (something such as UK, Europe, USA, UAE, etc. will suffice).

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1