Question re Vlookup function within Excel.

  oo7juk 16:34 05 Nov 2007
Locked

Hi,

Can the following be done.

I have the following formula in Sheet1 of my workbook.

=VLOOKUP(B6,Sheet2!A2:BC499,5,)

When cell A2 of Sheet2 is populated with the name John and cell E2 contains 'ABC' the formula works ok, but when cell A3 is populated with the same name, but cell E3 has 'DEF' it won't display the text 'DEF'. Can this be done so that each time I enter the same name in column A with different data in column E it populates correctly.

Many thanks.

  VoG II 16:37 05 Nov 2007

See 'Arbitrary Lookups' click here

  oo7juk 23:12 05 Nov 2007

VoG, tried the following, but no luck.

=INDEX(Sheet2!A2:E500,SMALL(IF(Sheet2!A2:B500=B6,ROW(A2:B500)-ROW(A2)+1,ROW(B500)+1),C21),2)

Also tried -

=MATCH(B$6,Sheet2!$A$2:$E$500,0),MATCH(Sheet2!B$6,$A$2:$E$500,0)

Thanks for your assistance so far VoG.

  oo7juk 10:44 07 Nov 2007

Update- Have been told that maybe vlookup is the wrong function and that excel can't do what I want it to do.

Would VBA help, for info can provide workbook for assistance.

MAny thanks.

  VoG II 17:26 07 Nov 2007

I would try posting this on MrExcel if you haven't already.

  oo7juk 18:51 07 Nov 2007

Thanks for advice VoG, will do.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

WPA2 hack: How secure is your Wi-Fi?

HP’s new Surface Pro rival is designed specifically for Adobe-using designers and artists

Best kids apps for iPhone & iPad

Que faire si son iPhone ou iPad est tombé dans de l'eau ?