Two-Column Lookup
 

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.

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.

"RJF" wrote:

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.

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

It's working great!

Thanks again.

"Govind" wrote:

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.

"RJF" wrote:

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.