Multiple Look Up
 

Hi
I would like to get a look up formula to look at multiple cells, for
example, I have London in A1 and transport in B1. Then London in A2 and
Design in B2 This goes down to A800 and B800 with different locations and
roles.

I then have a list of towns and roles showing their site number, D1 london,
E1 transport, F1 is the site number 1.

I want my formula to see if A1 matchs D1, if E1 matchs B2 show F1.

I then want to drag this down to match all the different types, so it will
show the corresponding site number each time.

I hope that makes sense!

Thanks

Multiple Look Up
 

This makes perfect sense until you say drag down to corresponding site
number. You might need a little more explaining for people to
understand that. The first part is easy. For instance
=if(A1=D1,if(E1=B2,F1,"Whatever error you want"),"Whatever error you
want")

This may get you started anyways

Multiple Look Up
 

If I understand correctly, try:

=index($j$1:$j$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

Enter with Ctrl+Enter+Enter

You will get {} round the formula.

Change ranges to suit.

Copy down.



"Fiona" wrote:

Hi
I would like to get a look up formula to look at multiple cells, for
example, I have London in A1 and transport in B1. Then London in A2 and
Design in B2 This goes down to A800 and B800 with different locations and
roles.

I then have a list of towns and roles showing their site number, D1 london,
E1 transport, F1 is the site number 1.

I want my formula to see if A1 matchs D1, if E1 matchs B2 show F1.

I then want to drag this down to match all the different types, so it will
show the corresponding site number each time.

I hope that makes sense!

Thanks

Multiple Look Up
 

typo ...

=index($F$1:$F$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

"Toppers" wrote:

If I understand correctly, try:

=index($j$1:$j$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

Enter with Ctrl+Enter+Enter

You will get {} round the formula.

Change ranges to suit.

Copy down.



"Fiona" wrote:

Hi
I would like to get a look up formula to look at multiple cells, for
example, I have London in A1 and transport in B1. Then London in A2 and
Design in B2 This goes down to A800 and B800 with different locations and
roles.

I then have a list of towns and roles showing their site number, D1 london,
E1 transport, F1 is the site number 1.

I want my formula to see if A1 matchs D1, if E1 matchs B2 show F1.

I then want to drag this down to match all the different types, so it will
show the corresponding site number each time.

I hope that makes sense!

Thanks

Multiple Look Up
 

Thank you, that formula does look like what I want, but it comes up with
#N/A, even with the { }

Any ideas?
Thank you

"Toppers" wrote:

typo ...

=index($F$1:$F$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

"Toppers" wrote:

If I understand correctly, try:

=index($j$1:$j$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

Enter with Ctrl+Enter+Enter

You will get {} round the formula.

Change ranges to suit.

Copy down.



"Fiona" wrote:

Hi
I would like to get a look up formula to look at multiple cells, for
example, I have London in A1 and transport in B1. Then London in A2 and
Design in B2 This goes down to A800 and B800 with different locations and
roles.

I then have a list of towns and roles showing their site number, D1 london,
E1 transport, F1 is the site number 1.

I want my formula to see if A1 matchs D1, if E1 matchs B2 show F1.

I then want to drag this down to match all the different types, so it will
show the corresponding site number each time.

I hope that makes sense!

Thanks

Multiple Look Up
 

#N/A means it cannot find a match: this would include extra blanks in a
matchhing field.

If you want, send sample w/book to:

toppers <at NOSPAMjohntopley.fsnet.co.uk

remove NOSPAM

"Fiona" wrote:

Thank you, that formula does look like what I want, but it comes up with
#N/A, even with the { }

Any ideas?
Thank you

"Toppers" wrote:

typo ...

=index($F$1:$F$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

"Toppers" wrote:

If I understand correctly, try:

=index($j$1:$j$100,match(1,($d$1:$d$100=a1)*($E$1: $e$100)=b1),0)

Enter with Ctrl+Enter+Enter

You will get {} round the formula.

Change ranges to suit.

Copy down.



"Fiona" wrote:

Hi
I would like to get a look up formula to look at multiple cells, for
example, I have London in A1 and transport in B1. Then London in A2 and
Design in B2 This goes down to A800 and B800 with different locations and
roles.

I then have a list of towns and roles showing their site number, D1 london,
E1 transport, F1 is the site number 1.

I want my formula to see if A1 matchs D1, if E1 matchs B2 show F1.

I then want to drag this down to match all the different types, so it will
show the corresponding site number each time.

I hope that makes sense!

Thanks