VLOOKUP from 2 values rather than 1?

[gohan2091]

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.



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

 

[barry houdini]

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]

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.

 

[gohan2091]

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]

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

 

[hiker95]

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
	A	B	C	D	E	F	G
1
2
3	Section	Item	Item Discription		Section	Item	Item Discription
4	100	001	TESTING 1		100	001	TESTING 1
5	100	002	TESTING 2		200	004	TEST 995
6	100	003	TEST 209
7	100	004	TEST AAA
8	100	005	TEST 1986
9	200	001	TEST 99
10	200	002	TEST 321
11	200	003	TESTING FFC
12	200	004	TEST 995
13
Sheet1

 

[gohan2091]

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.

 

[Aladin Akyurek]

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]

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]

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...