VLOOKUP from 2 values rather than 1?

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
Hi,

I need to find out how to do this for work and I've been trying various things for the last 45 minutes to no avail. :( In this small example, I have a data table and a separate lookup table beside it. I want the lookup table to return values from the data table. The last columns value (G Comlumn) in the lookup table needs to be determined based on what's being entered in the first two columns (E and F).

Here is my formula for the last column of the lookup table:

=VLOOKUP(E4,A4:C30,3,FALSE)

I need it to consider BOTH the values in E4 and F4 before returning the value so I wrote this formula:

=VLOOKUP(E4&F4,A4:C30,3,FALSE)

but it comes back with an error. I also tried:

=VLOOKUP(E4/F4,A4:C30,3,FALSE)

but of course, it will divide E4 and F4 together...

To help explain what I need doing, I have uploaded an image below and a link to the xls file. Can anyone help? I am an experienced Excel user but I only perform simple tasks. The sooner someone can help, the better so please if you know how to help, please do. :biggrin:



http://rapidshare.com/files/413271653/VLOOKUP_Example.xls
 
Last edited:

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Hello gohan2091, welcome to MrExcel,

I can't view your image but assuming E4 needs to match column A and F4 column B try this formula in G4

=INDEX(C$4:C$30,MATCH(1,INDEX((A$4:A$30=E4)*(B$4:B$30=F4),0),0))
 

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
Hello gohan2091, welcome to MrExcel,

I can't view your image but assuming E4 needs to match column A and F4 column B try this formula in G4

=INDEX(C$4:C$30,MATCH(1,INDEX((A$4:A$30=E4)*(B$4:B$30=F4),0),0))
Thanks for the welcome. I am not sure why you cannot view my image, you should be able to just click it... works for me. If clicking does nothing for you, here is the URL: http://www.postimage.org/image.php?v=PqcpEWJ

Your post has just made me aware that I haven't explained ALL that I need to do. Oops.. I forgot to mention that the text in red (Columns E and F) will be user determined e.g. a user will manually enter the secton and item numbers and the value in the third column must automatically update according to what's displayed in the data table. Your formula works and updates column 3 if the user changes what's in the item column, however if you change the section column to say 200, the formula breaks.
 
Last edited:

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
Have I lost you Barry? I really thought I would have the answer by now (considering how fast you replied to my message... thanks for that by the way!).

I tried a new formula but it doesn't work:

=VLOOKUP((E4:F4),A4:C30,3,FALSE)

This sure is frustrating!
 

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
Sorry for all the posts but this forum doesn't allow me to edit my post after 10 minutes. Just thought I found the solution to my problem but it appears the formula I have listed below isn't going to work in Excel 2003 (only suitable for 2007 and above).

=IFERROR(VLOOKUP(E4&"-"&F4,A4:C30,3,FALSE),"")

I cannot say for sure if this will do what I want but if it does, can someone adapt this for 2003 use? Probably need an ISERROR function but I do not understand what I am doing :LOL:
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,648
gohan2091,


I can not explain it exactly, but there seems to be some inconsistencies with your merged cells in columns A, B, and C.


If you were to remove all the merged cells, and remove all the extra rows, you will get this:



Excel Workbook
ABCDEFG
1
2
3SectionItemItem DiscriptionSectionItemItem Discription
4100001TESTING 1100001TESTING 1
5100002TESTING 2200004TEST 995
6100003TEST 209
7100004TEST AAA
8100005TEST 1986
9200001TEST 99
10200002TEST 321
11200003TESTING FFC
12200004TEST 995
13
Sheet1
 
Last edited:

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
gohan2091,


I can not explain it exactly, but there seems to be some inconsistencies with your merged cells in columns A, B, and C.


If you were to remove all the merged cells, and remove all the extra rows, you will get this:
HIKER, I cannot actually read your message because Symantec Web Security at my workplace had blocked it. I also could no longer reply from work because of this stupid web security thing!. I am at home now and can now read your message. When I return to work tomorrow though, I will be blocked off because of the code you have in your message. :(

I do not understand what you mean by inconsistencies with my merged cells. I see nothing wrong here. This example is based on a very large lookup table containing thousands of enteries, all using merged cells. I really need to leave the example as it is or it won't carry over to my actual work. What are those G4 and G5 tables? I inputted the G4 formula into my G4 cell but it doesn't work when I change the section from 100 to 200 on the first row. I need both the section and item columns to be fully adjustable. Sometimes the section will be 200 with an 010 item number, sometimes the section will contain a 1200, with item numbers ranging from 001 to 080... both columns will contain different values.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
B16, B17, B18 are all 5 (while B17 is displayed as 005) and a single value from C16 (merged with C17 and C18), i.e., TEST 1986, is associated with that B-entry. This pattern does not hold for B28, B29, and B30 which house 6, 4 (displayed as 004), and 6 respectively. This set of three is supposedly associated with TEST 995 in the C-range with C28 (merged with C29 and C30). That's the reason why the suggested formula or an equivalent form like for the lookup values of E5 and F5...

=INDEX($C$4:$C$30,MATCH(F5,IF($A$4:$A$30=E5,$B$4:$B$30),0))

won't yield what you seem to expect.
 

gohan2091

New Member
Joined
Aug 16, 2010
Messages
7
B16, B17, B18 are all 5 (while B17 is displayed as 005) and a single value from C16 (merged with C17 and C18), i.e., TEST 1986, is associated with that B-entry. This pattern does not hold for B28, B29, and B30 which house 6, 4 (displayed as 004), and 6 respectively. This set of three is supposedly associated with TEST 995 in the C-range with C28 (merged with C29 and C30). That's the reason why the suggested formula or an equivalent form like for the lookup values of E5 and F5...

=INDEX($C$4:$C$30,MATCH(F5,IF($A$4:$A$30=E5,$B$4:$B$30),0))

won't yield what you seem to expect.
B16, B17 and B18 are ALL 5, it's just 16 and 18 are set to hidden (font same colour as background) while 17 is shown. B16, 17 and 18 are all formatted as a 3 digit number. I see some inconsistancies with the item column on the 200 sections though but this is irrelvant as the sheet I uploaded is just an example, not my actual work. This has however caused the original formula you gave me to not work so I guess it was worth mentioning.

This formula does not work:

=INDEX($C$4:$C$30,MATCH(F4,IF($A$4:$A$30=E4,$B$4:$B$30),0))

but this one now works:

=INDEX(C$4:C$30,MATCH(1,INDEX((A$4:A$30=E4)*(B$4:B$30=F4),0),0))

I think I can adjust the formula now for my actual worksheet so thanks very much!
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
B16, B17 and B18 are ALL 5, it's just 16 and 18 are set to hidden (font same colour as background) while 17 is shown. B16, 17 and 18 are all formatted as a 3 digit number. I see some inconsistancies with the item column on the 200 sections though but this is irrelvant as the sheet I uploaded is just an example, not my actual work. This has however caused the original formula you gave me to not work so I guess it was worth mentioning.

This formula does not work:

=INDEX($C$4:$C$30,MATCH(F4,IF($A$4:$A$30=E4,$B$4:$B$30),0))

but this one now works:

=INDEX(C$4:C$30,MATCH(1,INDEX((A$4:A$30=E4)*(B$4:B$30=F4),0),0))

I think I can adjust the formula now for my actual worksheet so thanks very much!
You need to confirm the formula with control+shift+enter, not just with enter. They are equivalent, while the former a tad faster...
 

Forum statistics

Threads
1,084,878
Messages
5,380,415
Members
401,673
Latest member
Ali Balleya

Some videos you may like

This Week's Hot Topics

Top