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


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

Hi,

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

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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 start
off )
It will be greatly appreciated.

Andrewm


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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 wrote:

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

--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

--

Dave Peterson

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

andrewm


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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"
=IF(ISNUMBER(vlookup(match("andrew",A1:E1,0),A7:B1 1,2)),vlookup(match("andrew",A1:E1,0),A7:B11,2),"" )
or simply
=IF(ISNUMBER(MATCH("andrew",A1:E1,0)),vlookup(matc h("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


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=376695

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


--
andrewm
------------------------------------------------------------------------
andrewm's Profile: http://www.excelforum.com/member.php...o&userid=23130
View this thread: http://www.excelforum.com/showthread...hreadid=376695