Two-Column Lookup


G

Guest

I am trying to lookup information in a 3 column table based on data in
another table on a different tab. In the example, I want to look up G4 and
H4 on the first sheet, find the matching data in cells in columns B and C and
bring in the value from column D back to the first sheet in column I.

I'm using this formula: =INDEX('Update DB Ranges'!D21:D24,MATCH(1,('Update
DB Ranges'!B21:B24=G4)*('Update DB Ranges'!C21:C24=H4),0))

But I keep getting a #VALUE in cell I4 when I want to be getting 10.

Example:
1st Sheet
G H I
4 MS102 PREPAID OTHER =INDEX('Update DB Ranges'!D21:D24,MATCH(1,('Update DB
Ranges'!B21:B24=G4)*('Update DB Ranges'!C21:C24=H4),0))

Information to look up on other sheet which is called Update DB Ranges

B C D
21 MS102 PREPAID OTHER 10
22 MS103 ACCRUED VACATION 20
23 MS103 ACCRUED BONUS / COMMISSIONS 30
24 MS104 ACCRUED OTHER 40

Can anyone please help me figure out what I'm doing wrong?

Thank you so much.
 
Ad

Advertisements

G

Guest

Hi,

This formula works fine for me:

=INDEX('Update DB Ranges'!$D$21:$D$24,MATCH(1,('Update DB
Ranges'!B21:B24=G4)*('Update DB Ranges'!C21:C24=H4),0))

But this formula should be entered using Ctrl + Shift + enter and not just
enter as this is an array formula.

Try this and let me know if this doesnt work.

Govind.
 
Ad

Advertisements

G

Guest

Thank you so much. I feel like such an idiot. I did not use the Control +
Shift + Enter.

It's working great!

Thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

two column lookup 3
two-column lookup 7
Lookup Two Columns 5
Lookup two different columns 10
Difficult Two Column Lookup 0
Difficult Two Column Lookup 2
Lookup Two Columns - Again 5
lookup using two columns 3

Top