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

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

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

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


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

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(matc h("andrew",A1:E1,0),A7:B11,2),"")

many 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

=IF(ISNUMBER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(M ATCH("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 a 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(ISNUM BER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(MATCH("abd ul",$A$1:$E$1,0),$A$7:$C$11,3),""))


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

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(ISNUM BER(MATCH("abdul",$A$1:$E$1,0)),VLOOKUP(MATCH("abd ul",$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


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

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)),VLOO KUP(MATCH("andrew",$D$2:$D$4,0)+2,$A$7:$B$11,2),IF (ISNUMBER(MATCH("abdul",$D$2:$D$4,0)),VLOOKUP(MATC H("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 D2:D4


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

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


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

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


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

Hi Mangesh,

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

many 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

Sure.

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