Solved: VLOOKUP in multiple arrays

[white_flag]

today outside is really nice (but I have to stay inside) ..anyway, I need your help. so my questions are:

it is possible to use vlookup in multiple arrays? =VLOOKUP(cellref;rmultiple arrays;column;0)
or it is possible if I have an name that it is starting with an prefix "R-" to search in array "Rockwool", "F-" to search in array "Foamglaas" etc.

thank you :)

[mbarron]

Here is one way:

=VLOOKUP(A1,INDIRECT(VLOOKUP(LEFT(A1,2),Table,2,0)),2,0)
Where A1 contain your lookup value (R-U-Happy for example) and Table is a list of the prefixes with their corresponding Named range.


R- Rockwool
F- Foamglass
G- Glassfoam

[white_flag]

thx, mbarron

I quess "Table" can not be an list without to be made in excel? like this: substitute "Table" with direct "Rockwool, Foamglass ..etc"

[Bob Phillips]

Yes, but R- is not a valid name, R_ is

VLOOKUP(LEFT(A1,2),{"R_","Rockwool";"F_","Foamglass"},2,FALSE)

[mbarron]

The attachment uses the following formula.

=VLOOKUP(MID(B1,3,99),INDIRECT(VLOOKUP(LEFT(B1,2),prefix,2,0)),2,0)

My assumption is that you have a value like F-A123 and want to find the corresponding value for the A123 part in a table called Foamglass. I've also assumed you could have a value like R-A123 that you need to find the correspond value for A123 in a table called Rockwool. The prefix table is structure in the following manner.


R- Rockwool
F- Foamglass
A- another table
S- some other table
Y- yet another table



It may be best if you post a sample of your workbook.

[white_flag]

thank you: xld & mbarron

this solution I was looking for:
VLOOKUP(LEFT(A1,2),{"R_","Rockwool";"F_","Foamglass"},2,FALSE)

have an lovely day.

[white_flag]

ok .. I was think that I can fix this alone but I can not figure out:

=VLOOKUP(I5;(LEFT(I5;2);{"R_";"Rockwool";"F_";"Foamglass"});2;0)


this part can not resolve the name:

LEFT(I5;2);{"R_";"Rockwool";"F_";"Foamglass"}

[Bob Phillips]

You can't have all ; in the 2D list, even in a continental version. This is guessing a bit, but try



=VLOOKUP(I5;(LEFT(I5;2);{"R_"."Rockwool";"F_"."Foamglass"});2;0)


assuming that . is a list separator. If not, look in Control Panel>Regional Settings, or post it here, if we change it, when you open it it should auto-update it.

[mbarron]

Please post a workbook

You formula

=VLOOKUP(I5;(LEFT(I5;2);{"R_";"Rockwool";"F_";"Foamglass"});2;0)
breaks down to
Lookup value in I5 in the table left(I5,2) and an array,bring back the second column of the (invalid) table where the value in I5 matches a value in the first column of your (invalid) table.

[Bob Phillips]

The problem is that he has a continental version of Excel, which means that it uses a semi-colon (;) as a list sepaerator, not a comma (,) as yours and mine does.

[white_flag]

it is possible an solution without Regional settings because on my work I do not have rights to change anything in my computer.

please see the attachment file.

[mbarron]

This perhaps

=LOOKUP(LEFT(I5,2);{"R_";"F_"};{"Rockwool";"Foamglass"})

[white_flag]

:) nice and perfect ..
thank you very much.

[Bob Phillips]

You don't need to change anything, just lookup what the settings are.

Anyway, try this and see if it works for you

[white_flag]

works very well..thank you

[white_flag]

ok ..again I was in the hurry and I didn't check entire formula now the problem is like this:
it can not be solve the string that contains "Rockwool" ..it is been put with commas and like this give the #VALUE! error ..
so, what can be done?

[mbarron]

You can use the Named Range as part of the function - which you are doing in cell I6. If you want to refer to the table as the result of a formula, like you are attempting in I7 (or as a value in another cell), you need to wrap the formula (or cell reference) in the INDIRECT() function.


=VLOOKUP(I5,INDIRECT(LOOKUP(LEFT(I5,1),{"R";"F"},{"Rockwool";"Foamglass"})),2,0)

[white_flag]

finally, this is solved. thx a lot and thx for the explication.