Vlookup Function

[=BA=Spike]

Hi

Im trying to use the vlookup function.

The problem I have is, I need it to do the lookup over three rows.
(i.e. A1:C1) with the prospect of only having values in one row, or
having more than one row populated. It would then check a range, and
return a value.

i.e. =VLOOKUP(A1:C1,D1:M21,6,False)

But this does not work.

Is there a way of having it lookup a value between A1:C1 and then do
the rest of the process?

Thanks

Spike

 

[Frank Kabel]

Hi
not really sure what you're trying to achieve. But maybe
the following array formula is what you're looking for (to
be entered with CTRL+SHIFT+ENTER):

=INDEX(M1:M21,MATCH(1,(A1=D121)*(B1=E1:E21)*
(C1=F1:F21),0))

 

[JulieD]

Hi Spike

are you saying that you want to lookup a value in a cell and the cell could
be in either A1, B1 or C1 -
what do you want to happen if there is a value in A1 which is different to
the value in B1 & the value in C1, which one do you want to use in the
VLOOKUP or will this never happen?

anyway you can do something like this:
=IF(ISNA(VLOOKUP(IF(A1="",IF(B1="",C1,B1),A1),D1:M21,6,False)),"",VLOOKUP(IF
(A1="",IF(B1="",C1,B1),A1),D1:M21,6,False))

this will use a value in A1 if there is one, if not it will take the value
from B1, if there isn't a value in B1 it will look in C1 and then do the
vlooked - if there's no value in any of them it will return a zero length
string.

let us know how you go

Cheers
JulieD

 

[=BA=Spike]

Hi

What Im trying to do is...

I have 2 worksheets, with similar data in them, same heading etc.
Im trying to populate the 2nd worksheet, with the data from the first


I have data in A1:C1 say, on both worksheets, (That is, say B1 has th
data, A1 and C1 are blank on the 2nd worksheet)
On worksheet 1, I have data in B1 and in D1,E1,F1. I need to populat
D1,E1,F1 in worksheet 2.

Now the hard bit is, I would like to have a generic formula, tha
checks if there is data in A1, B1, C1 on ws1, and in ws2 and the
populates the other cells correctly. But the data in A1,B1,C1 migh
only be in one cell, two of them, or even all three.

Is there a way to have it check if there is something in any of th
cells, then check on ws2, and populate the rest of the desired cells?

Spik

 

[JulieD]

Hi Spike

okay let me see if i've got this right
sheet 1

A B C D E F
1 cat meow furry
whiskers
2 dog woof hairy big
ears
3 mouse squeak small long tail


sheet 2

A B C D E F
1 cat
2 mouse
3 dog

and you want the cat stuff in Sheet2!D1:F1
the mouse stuff in sheet2!D2:F2
and the dog stuff in sheet2!D3:F3

-------
is this correct?
is it possible to change the structure of the worksheets in any way -
particularly by the inclusion of a hidden column between column C & D on
both sheets - as this might provide the easiest solution?
but do let us know if this is what you're after first
Cheers
julieD

 

[=BA=Spike]

Ok...

A1, B1, C1, D1, E1, F1, G1, H1,
10, , , 100, 50, , , 24,
, 2, 24, , , 23 , 11,
, , 2, 2, , , 54, ,

I might have something looking like this for ws1.
In ws2 I have the same values in A1:C1 and nothing in D1:H1

So, I was hoping I could do a lookup on ws2, D1 to check if there wa
anything in the range A1:C1 on ws1. If there is then return the valu
of 100 to D1 on ws2

And so on for the rest.

Thanks for the help

Spike

edit: And I could add a hidden col if required :

 

[=BA=Spike]

See attched fil

Attachment filename: test.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=61730

 

[JulieD]

Hi Spike

if you have exactly the same layout in sheet 2 as for sheet 1 (for columns A
to C) can't you just use the following formula in Sheet2!D1
=Sheet1!D1
and fill for the rest of them?

Cheers
JulieD

 

[=BA=Spike]

That would be nice to do.

However, sht 2 is not always the same, it can contain some of the dat
from sht1, but it might contain some more. And might not be in the sam
order.

Sht2 is basically a report, where sht1 is what helps to populate it
but the contents in sht1 may not be in the same order, or have the sam
amount of lines as sht2, as sht2 is firstly populated with data fro
another source.

Spik

 

[JulieD]

so in that case what do you want to do if say both A1 and C1 on sheet 2 are
populated with say 10 and 20 ... do you want to find a match on sheet 1
where A1 = 10 and C1 = 20 or could B1 = 10 and C1 = 10 or if 10 is found in
A1 is that enough?

 

[=BA=Spike]

I think, all i need is a match in any of the cells, either A1,B1,C1 etc


So, if there is a match in A1, then it would populate the relevan
values on sht2. If A1 does not match, then it should check B1, if B
does not match then it should check C1 etc.

Thanks

Spike;

 

[JulieD]

Hi Spike

in that case i would say the easiest way to achieve what you want is to
insert a column in both sheet 1 & 2 (column D) and type for both
=IF(A1="",IF(B1="",C1,B1),A1)
and fill down for all rows

then on sheet 2 for column E
type
=IF(VLOOKUP($D1,Sheet1!$D$1:$I$4,2,FALSE)=0,"",VLOOKUP($D1,Sheet1!$D$1:$I$4,
2,FALSE))
copy across to rows F, G, H, I
changing the ,2, to ,3, for column F; ,4, for column G; ,5, for column H &
,6, for column I (ie the third parameter)
in both VLOOKUP statements
and then fill down all rows

i'm not sure it will work for all combinations and variations possible but
might get you somewhere -hopefully someone else will have a more generic
solution if required.

Cheers
JulieD