Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I sort by row instead of by column? | Excel Worksheet Functions | |||
Lookup last in column formulas | Excel Discussion (Misc queries) | |||
How to lookup data in a row and column | Excel Discussion (Misc queries) | |||
How to lookup data in a row and column | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |