Formula Help Please (1 Viewer)

[HaroldIII]

Column A has a list of brokers (some brokers comes up multiple times approx 20 different Names)
Column B has a list of Clients (No Duplicate clients here, approx 150 different names)
On the second Tab If cell any cell in E2:E25 has a client name, then F2:F25 needs to have the matching broker...

Please help.

thanks

 

[NBVC]

Try something like:

=INDEX('Sheet1'!A:A,MATCH(E2,'Sheet1'!B:B,0))

copied down

where Sheet1 is the name of your database sheet

 

[HaroldIII]

Try something like:

=INDEX('Sheet1'!A:A,MATCH(E2,'Sheet1'!B:B,0))

copied down

where Sheet1 is the name of your database sheet

I am still haveing an error....am i doing this right???

please check the attachment...

thanks again

 

[NBVC]

Can't open the attachment properly.

 

[Rabbie]

I am still haveing an error....am i doing this right???

please check the attachment...

thanks again

Formula =INDEX(Sheet1!B$1:B$8,MATCH(E2,Sheet1!A$1:A$7,0)) gives correct values.

 

[Brianwarnock]

NBVC 's formula works,
Rabbie's has the columns the wrong way as he is typing in the client and wants the broker.

Pity he didn't say what the error was and provide a readable attachment.

Brian

 

[zoiboi]

Since the clients are unique (no dupes) and in first position you could also use a vlookup

+VLOOKUP(E2,Sheet1!A:B,2,0)

and it is a little more straightforward.

Have fun!

 

[Rabbie]

The client names were NOT unique in the DB posted in #3. That spreadsheet worked OK for me using Excel 2007. I edited the names on sheet 1 so I could check I was accessing the correct row.

 

[NBVC]

If the OP actually cared enough to come back.. we would get it straight, I think...

anyhow, I did get it open in 2007 now... and

=INDEX(Sheet1!B:B,MATCH(E2,Sheet1!A:A,0))

or

=VLOOKUP(E2,Sheet1!A:B,2,FALSE)

both should get the result based on the spreadsheet which is not the same as the original descripition....imo.