array table and vlookup

[Guest]

I have a spreadsheet that has a cell that does a vlookup of a value and looks
in another spreadsheet to pull different data. so spreadsheet A looks like
this:
RACKA and spreadsheet B has RACKA Room1 Panel 4 and once i input RACKA it
pulls information from another speadsheet to give me Room1 Panel 4 and device
name. My problem is that I have RACK A listed several times in Spreadsheet B
but different Panel #'s and different device names but because my array table
is the same it keeps giving the first line of the Array table as the result.
I can change my formula in spreadsheet A for every line but then it would
defeat the purpose of having the formula calculate the result.

 

[Guest]

VLOOKUP is a good way to find the FIRST occurance of an item in the table.
To get ALL the occurances, the usual technique is to use AutoFIlter on the
table and copy over the results. This is not really automatic.

If the table includes row information, then it is possible to create a set
of cascading VLOOKUP formulas.

The first VLOOKUP would cover the entire table, the second VLOOKUP would
begin one row below what the first one found, etc.

 

[Guest]

That is my problem. I don't want to change every formula in the 1st
spreadsheet. Can the formula have the array table from the other spreadsheet
increment by one if i copy it down the column. Or can i do something like if
column A is found in Spreadsheet B then look at column B and search
Spreadsheet B to find a match and place the value.

 

[Guest]

THis is my example
Spreadsheet A

Column A Column B Column C Column D
1 RACK A Room 1 Panel 1 DEVICEA
2 RACK A Room 1 Panel 2 DEVICEB


Spreadsheet B

RACK A Room 1 Panel 1 DEVICEA
RACK A Room 1 Panel 2 DEVICEB

So on spreadsheet A i would only enter RACKA and it would look in
Spreadsheet B and automatically enter Room 1 (as these were predefined).
When i input Panel 2 it should look at the rack and panel and then input
DEVICE in spreadsheet A. My problem is when i use the same VLOOKUP formula
and copy it to my spreadsheet A it keeps looking up device on the 1st line of
the spreadsheet B.

 

[Pete_UK]

Saved from a posting last night:

Can you post details of how your data is laid out, and what formulae
you are using?

One way of approaching this is to detect if there are duplicates
(i.e.
IF(A2=A1, ...) assuming column A contains your sought-values, and in
the
THEN part of the IF adjust the range of the table that the VLOOKUP
function is using, and in the ELSE part you have your normal VLOOKUP
function. Adjusting the table range is done by means of INDIRECT,
with
a MATCH function which determines which row of the table the previous
name occurs on. I'm sure you can see that the formula needs to be
fitted to suit the actual data layout that you are using.

....

However, it can be done with one formula, made easier if all your RACK
A in sheet A are in consecutive cells. If they are not it can still be
done, but using COUNTIF.

Hope this helps.

Pete

 

[Pete_UK]

Now I can see your example, another way is to insert a new column D in
sheet B and in D1 enter this formula:

=A1&B1&C1

and copy this down for as many entries as you have (you can hide the
column if you want to). Your VLOOKUP formula in sheet A will now be
something like:

=VLOOKUP(A1&B1&C1,SheetB!D$1:E$100,2,0)

and can be copied down as required.

Hope this helps.

Pete

 

[Guest]

THANK YOU SO MUCH! IT WORKED!

Now I can see your example, another way is to insert a new column D in
sheet B and in D1 enter this formula:

=A1&B1&C1

and copy this down for as many entries as you have (you can hide the
column if you want to). Your VLOOKUP formula in sheet A will now be
something like:

=VLOOKUP(A1&B1&C1,SheetB!D$1:E$100,2,0)

and can be copied down as required.

Hope this helps.

Pete

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

THANK YOU SO MUCH! IT WORKED!






- Show quoted text -