Find Value in range

[nav1701]

Hi,

I need to do a lookup to bring back a value with 2 sets of crieria.

the table I'm searching against has headings across the top and unique values which need to be searched against in the left hand column.

I the sheet I am in, I need to seach for a value in cell B4 against the values listed in the second sheet in the left hand column, then I need to seach against the headings to match the value in F2. Is this possible?

Any help you could offer would be great.

 

[mikerickson]

This should work
=INDEX(Sheet2!$A$1:$Z100, MATCH(Sheet2!B4, Sheet1!$A$1:$A$100,0), MATCH(Sheet2!F2, Sheet1!$A$1:$Z$1,0))

 

[nav1701]

Thanks, almost there, what if the value in the furtherest left column in the second sheet is listed more than once?

 

[mikerickson]

What happened when you tried it?
What do you want to happen?

 

[nav1701]

It worked perfectly fine, but I need to do a similar thing in another spreadsheet, but with this one the values in the left hand column can come up more than once.

 

[mikerickson]

The formula will return the value from the first row of Sheet1!A:A that matches Sheet2!B4.
If B4 has a Validation list, it will NOT distiquish between the first and second "Smith" in the list.
To get around that would involve VBA.

 

[Stephen_Tsui]

Try to use below array formula:

1) Input below formula:
= INDEX(Sheet2!$A$1:$Z100, LARGE(IF((Sheet1!$B$1:$B$100=Sheet2!$B$4),ROW(Sheet1!$B$1:$B$100),0),ROW(A1)),MATCH(Sheet2!$F$2, Sheet1!$A$1:$Z$1,0))

2) Then press ctrl + shift + enter

3) Then drag drown to cell below

All the cells Matched in the Left Column will been shown.

No existing data to try may get error, see if it what u need ,thx.