lookup more than one cell

[andrewm]

Is it possible to lookup on more than one lookup table simulatenously.
I was told to use lookup and not an if statement as I cannot use more
than 7 nested functions with it.
Sorry I will try to explain.

lookup statement is in say B1

if A1 has "andrew" then put in B1 "white" (with a lookup table)
if B1 has "andrew" then put in B1 "black" ( with a lookup table)

etc.

if statement is out as I have too many tested functions

thanks

andrewm

 

[mangesh_yadav]

Hi,

You could use a VLOOKUP. But your example is not clear, please explain
again.

Mangesh

 

[andrewm]

Sorry, mistake.

the result will be in say B2 (with the lookup) - this is dependent on
the fields A1 and B1.

if A1 has "andrew" then put in B2 "white" (with a lookup table)
if B1 has "andrew" then put in B2 "black" ( with a lookup table)

and so on

(? can you combine lookups in the same field)

andrewm

 

[mangesh_yadav]

In such a case, you are better off using an IF statement in cell B2.

=IF(A1="andrew","white",IF(B1="andrew","black",""))

Probably if you give your real case, someone would be able to suggest
you the best appraoch.

Mangesh

 

[Dave Peterson]

I'd use an extra column.

Use column B for the "intermediate" results and then use column C for the
"final" result.

If you want, you could hide column B.

 

[andrewm]

Thanks,

I have tried if statements, but I need more than 7 nested functions.
Could someone help.
Either with lookup or help me with vba ( give me an example to star
off )
It will be greatly appreciated.

Andrew

 

[mangesh_yadav]

Probably this is what you want.

in row 1, you have "andrew" in any of the cells A1:E1. In B2 you want
the color depending on the position of "andrew" in the above range.
Lets say you have a table in range A7:B11
Columns A7 onwards has
1,2,3,4,5
And B7 onwards has
white, black, color3, color4, color5

Enter in B2:
=VLOOKUP(MATCH("andrew",A1:E1,0),A7:B11,2)


Mangesh

 

[andrewm]

Thanks I will try the above

? I've asked before, but can someone help with vba -

to write the following if it helps me -

if a1 = "andrew" then b2 = "black"
if a2 = "john" then b2 = "white"


how do you write this in vba (module)

thanks

andrew

 

[mangesh_yadav]

One way is almost exactly how you have written it. i.e.
if range("a1") = "andrew" then range("b2") = "black"
if range("a2") = "john" then range("b2") = "white"

Another way
if range("a1") = "andrew" then
range("b2") = "black"
elseif range("a2") = "john" then
range("b2") = "white"
end if

Mangesh

 

[andrewm]

thanks mangesh - but a few issues
1. with vlookup(match("andrew",A1:E1,0),A7:B11,2) it works, but if
there is no "andrew" in the cells A1:E1 i get in B2 N/A. How can I
change it to have b2 a blank if there is no "andrew"

2. with the use of vba i wrote

if range("a1") = "andrew" then range("b2") = "black"
if range("a2") = "john" then range("b2") = "white"
end if

that is all I wrote and it did not work
what do I need to write before / after the above

(ps. any good book on vba)

andrewm

 

[mangesh_yadav]

1. with vlookup(match("andrew",A1:E1,0),A7:B11,2) it works, but if ther
is no "andrew" in the cells A1:E1 i get in B2 N/A. How can I change i
to have b2 a blank if there is no "andrew"

=IF(ISNUMBER(vlookup(match("andrew",A1:E1,0),A7:B11,2)),vlookup(match("andrew",A1:E1,0),A7:B11,2),"")
or simply
=IF(ISNUMBER(MATCH("andrew",A1:E1,0)),vlookup(match("andrew",A1:E1,0),A7:B11,2),"")

2. with the use of vba i wrote

if range("a1") = "andrew" then range("b2") = "black"
if range("a2") = "john" then range("b2") = "white"
end if

that is all I wrote and it did not work
what do I need to write before / after the above

You don't need the end if in this case.

Enter the following code in a standard module and run it.

sub test()
if range("a1") = "andrew" then range("b2") = "black"
if range("a2") = "john" then range("b2") = "white"
end sub


A link:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Manges

 

[andrewm]

thanks Mangesh,

just a question -

the macro works but only manually, how do I automatically make it work

also which is the best way to go - vba or with vlookup /match

thanks

andrewm

 

[mangesh_yadav]

Hi Andrew

the macro works but only manually, how do I automatically make it work

You can enter the code in the module of the concerned sheet in its
change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 And (Target.Column >= 1 And Target.Column <= 5) Then
' your code here
End If
End Sub

Note: .row=1 is for row 1, and columns 1 and 5 are A to E as the
example I gave was perhaps 5 column wide. Change this to suit
yourself.

also which is the best way to go - vba or with vlookup /match

As for this question, the best is which fits the situation the best. It
is usually said that what is not possible through worksheet formulae, go
in for VBA.

Mangesh

 

[andrewm]

Hi Mangesh or others - I have been using the stated formula for a
vlookup. However for this cell ( I will say it as I would say it in
english) if there is "andrew" in the stated cells return the respected
match, however if there is "abdul" in different cells I would like to
return a match based on a different vlookup.

=IF(ISNUMBER(MATCH("andrew",A1:E1,0)),vlookup(match("andrew",A1:E1,0),A7:B11,2),"")

many thanks

andrewm

 

[mangesh_yadav]

=IF(ISNUMBER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(MATCH("abdul",$A$1:$E$1,0),$A$7:$C$11,3),"")

I added one more column in the table which would be used to lookup for
abdul. Some clarifications required are: You will put the formula for
"andrew" in B2, right?
For "abdul", you want to use another cell, then the above formula can
be used.

Or is it that you want to first check what is in the cell, "andrew" or
"abdul", and then decide on which vlookup. Or if you don't know which
name will come, i.e. either could come, then use the following
formula:

=IF(ISNUMBER(MATCH("andrew",$A$1:$E$1,0)),VLOOKUP(MATCH("andrew",$A$1:$E$1,0),$A$7:$B$11,2),IF(ISNUMBER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(MATCH("abdul",$A$1:$E$1,0),$A$7:$C$11,3),""))


Mangesh

 

[andrewm]

Hi Mangesh,

yes the formula works ta.

what do i do if the cells for the name being inserted are not
continuous.

=IF(ISNUMBER(MATCH("andrew",$A$1:$E$1,0)),VLOOKUP(MATCH("andrew",$A$1:$E$1,0),$A$7:$B$11,2),IF(ISNUMBER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(MATCH("abdul",$A$1:$E$1,0),$A$7:$C$11,3),""))

what is the formula if the cells which "abdul" or "andrew" may go in
are not continous
eg abdul may go in say - A1,B1,D2,D3,D4

do I need two separate formulas

many thanks

andrewm

 

[mangesh_yadav]

Hi Andrew,

Although I believe that this formula could become shorter, but for the
moment:

=IF(COUNTA(A1:B1)>0,IF(ISNUMBER(MATCH("andrew",$A$1:$B$1,0)),VLOOKUP(MATCH("andrew",$A$1:$B$1,0),$A$7:$B$11,2),IF(ISNUMBER(MATCH("abdul",$A$1:$B$1,0)),VLOOKUP(MATCH("abdul",$A$1:$B$1,0),$A$7:$C$11,3),"")),IF(ISNUMBER(MATCH("andrew",$D$2:$D$4,0)),VLOOKUP(MATCH("andrew",$D$2:$D$4,0)+2,$A$7:$B$11,2),IF(ISNUMBER(MATCH("abdul",$D$2:$D$4,0)),VLOOKUP(MATCH("abdul",$D$2:$D$4,0)+2,$A$7:$C$11,3),"")))

This is also based on the premises that at a moment there would be only
one cell filled in the entire 5-cell range A1:B1 and D24


Mangesh

 

[andrewm]

Hi Mangesh,

sorry I've tried to keep it simple.

I know what I want to do practically, but in theory to TRY and keep it
simple
(i will change the cells)
the name "andrew" will go into one of the cells a1,a2 or a4 or a6
the name "abdul" will go into one of the cells d2,d3 or d5
the other cells without andrew or abdul will have another name (which
can be anything.

andrewm

 

[mangesh_yadav]

Can you give a snapshot of exactly how your sheet would look like, and
the expected answers as well. Give a case which addresses everything.

for instance it should show where Andrew is, where abdul is, which
cells will be filled. Where should be the answers .. each for andrew
and abdul.

Mangesh

 

[andrewm]

Hi Mangesh,

thanks, I think I have fixed it. I will post another question if i
doesn't work properly.

many thanks

andrew