Multiple Look Up

[Fiona]

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

 

[Bongard]

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

 

[Toppers]

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.

 

[Toppers]

typo ...

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

 

[Fiona]

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]

#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