View Full Version : Solved: VLOOKUP in multiple arrays
white_flag
04-08-2010, 07:43 AM
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
04-08-2010, 08:00 AM
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
04-08-2010, 08:10 AM
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
04-08-2010, 08:39 AM
Yes, but R- is not a valid name, R_ is
VLOOKUP(LEFT(A1,2),{"R_","Rockwool";"F_","Foamglass"},2,FALSE)
mbarron
04-08-2010, 08:40 AM
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
04-08-2010, 08:54 AM
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
04-08-2010, 09:33 AM
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
04-08-2010, 10:43 AM
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
04-08-2010, 11:09 AM
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
04-08-2010, 11:52 AM
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
04-08-2010, 11:59 AM
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
04-08-2010, 12:06 PM
This perhaps
=LOOKUP(LEFT(I5,2);{"R_";"F_"};{"Rockwool";"Foamglass"})
white_flag
04-08-2010, 12:11 PM
:) nice and perfect ..
thank you very much.
Bob Phillips
04-08-2010, 12:14 PM
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
04-08-2010, 12:19 PM
works very well..thank you
white_flag
04-08-2010, 01:33 PM
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
04-08-2010, 02:17 PM
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
04-08-2010, 02:23 PM
finally, this is solved. thx a lot and thx for the explication.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.