Can I combine multiple vlookups in one formula?

[Didymus]

Hi! First time posting, but have searched the forum and not found an answer to exactly what I'm trying to do. Maybe it's not possible.

I am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).

What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).

Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.

I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.

Any guidance will be much appreciated.

 

[Kaps_mr2]

Not entirely sure what you trying to do. But take a look at the FIND function.

Find("Pipe","Copper Pipe",1)

and also ISNUMBER(Find("Pipe","Copper Pipe",1)).

Kaps

 

[jim may]

Hi! First time posting, but have searched the forum and not found an answer to exactly what I'm trying to do. Maybe it's not possible.

I am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).

What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).

Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.

I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.

Any guidance will be much appreciated.

Welcome to the Board...

Your explanation is not clear to me. Stick with the first example and give more details of what each reference value is before running the formula; also provide the expexted formula output.

 

[Didymus]

Wow! Thanks for speedy replies. Sorry if my query wasn't too clear.

Basically, on Sheet 2 there is a table - column A thereof is dimensions in mms.
Column B is headed "pipes" and contains unique reference codes for pipes depending on their dimensions. Column C contains unique reference codes for "Bends" while Column D contains unique reference codes for Flanges. All these respective reference codes depend on the dimension which is in Column A.

On the master spreadsheet Column A will contain Pipe, Bend, Flange etc., (or variations of same), while column C contains the relevant dimensions of that given pipe, bend, or flange.

My hoped for output is to enter a formula in B2 on the master spreadsheet which will look up the table containing dimensions and reference codes and return the correct code to me - which is dependant on two conditions : what is it (pipe, bend, flange) and what are its dimensions in mms.

As I said, the vlookup works fine when there is only one lookup in the formula, but I have failed to get it to combine so that if it is "pipe" it will go to column 2, if it is "bend" it will go to column 3, "flange" to column 4 etc.

Any help will be much appreciated

 

[Kaps_mr2]

can you post a copy of your workbook ?

 

[Didymus]

Sorry, I can't figure out how to send you a copy of a worksheet. But this is how the main sheet looks:
A B C
Item Code Dimensions
Pipe L450 250
Bend A1234 50
Elbow E3456 450

etc.
The code in B2 (where L450 is returned) is =IF(A2="pipe",VLOOKUP(C2,Sheet2!$A$4:$B$12,2)) which means it looks up the dimensions on Sheet 2, matches it with C2 and returns L450 which is the value in in column 2 on sheet 2 matching 250 mm dimension.

What I'm trying to do is to expand the formula so that if the item in column A is "Bend" it will get the value from Column 3 of sheet 2 and if the item is column A is "elbow" it will perform the lookup in Column 4.

Many thanks.

 

[Aladin Akyurek]

Sorry, I can't figure out how to send you a copy of a worksheet. But this is how the main sheet looks:
A B C
Item Code Dimensions
Pipe L450 250
Bend A1234 50
Elbow E3456 450

etc.
The code in B2 (where L450 is returned) is =IF(A2="pipe",VLOOKUP(C2,Sheet2!$A$4:$B$12,2)) which means it looks up the dimensions on Sheet 2, matches it with C2 and returns L450 which is the value in in column 2 on sheet 2 matching 250 mm dimension.

What I'm trying to do is to expand the formula so that if the item in column A is "Bend" it will get the value from Column 3 of sheet 2 and if the item is column A is "elbow" it will perform the lookup in Column 4.

Many thanks.

=VLOOKUP(C2,Sheet2!$A$4:$D$12,MATCH(A2,Sheet2!$A$3:$D$3,0),0)

where B3:D3 on Sheet2 houses the following values: Pipe, Bend, and Elbow.

 

[Didymus]

Many thanks for that. It works perfectly.

My only difficulty now is that in some cases there may qualifications of the items in column A of the master sheet, i.e., "pipe" may become "copper pipe", but sheet 2 with the unique reference codes will only have a column for "pipe". That is why I was using the Search function to search for the text "pipe" before sending it over to look up sheet 2.

However, it is not a major problem as these instances can be entered manually, but in the ideal world ....

Thank you so much for your help - this was my first query to the Forum and I am very impressed.

 

[Aladin Akyurek]

Many thanks for that. It works perfectly.

My only difficulty now is that in some cases there may qualifications of the items in column A of the master sheet, i.e., "pipe" may become "copper pipe", but sheet 2 with the unique reference codes will only have a column for "pipe". That is why I was using the Search function to search for the text "pipe" before sending it over to look up sheet 2.

However, it is not a major problem as these instances can be entered manually, but in the ideal world ....

Thank you so much for your help - this was my first query to the Forum and I am very impressed.

For cases like copper pipe, etc...

Control+shift+enter, not just enter:

=VLOOKUP(C2,Sheet2!$A$4:$D$12,MATCH(9.99999999999999E+307,SEARCH(Sheet2!$B$3:$D$3,A2))+1,0)

where C2 houses a lookup value like pipe or copper pipe and A2 a dimesion value.

 

[Didymus]

Once again, thank you very much. It works perfectly.

Your efforts are much appreciated.



May you continue to excel in everything you do