How to return a second entry for same VLookup condition

Common problems – different language?
Let's talk in English.

How to return a second entry for same VLookup condition

Poslaťod Palo-admin » 29.09.2009, 11:23

Rosalind McG : Help - how to look up and return a second entry for same VLookup condition

eg
Sheet C : in cell A1 has the lookup value EF45678 with an entry of 914.25 in cell B1
in cell A2 has the lookup value EF45678 with an entry of -25.76 in cell B2

At the moment I have a Vlookup function that will search Sheet D for EF45678 and return the first entry (as data sorted in decending order) ie will return 914.25 in cell C1 in sheet C.
However, it also gives me 914.25 in cell C2, instead of -25.76.

In this example there is only two instances of EF45678 but there could be more in each sheet or a mismatch of occurences ie 3 instances in Sheet C and 2 in Sheet D (or vice versa) .
Can I adapt my VLookup formula or does this require VBA (of which I have no knowledge) ?

Shortly (note: searched source must be sorted):
via VLOOKUP
1) Find first - VLOOKUP(B3;$F$3:$G$6;2;FALSE)
2) Find last - VLOOKUP(B3;$F$3:$G$6;2;TRUE)

via OFFSET and MATCH
1) Find first - OFFSET($F$2;MATCH(B8;$F$3:$F$6;[color=#FF0000]0);1)[/color]
2) Find 2-nd - OFFSET($F$2;MATCH(B8;$F$3:$F$6;[color=#FF0000]0)+1;1)[/color]
3) Find last - OFFSET($F$2;MATCH(B8;$F$3:$F$6;[color=#FF0000]1);1)[/color]

Certainly, more complex formula is required to test, if is there more than one found value.
E.g. test result of MATCH fuction with 0 and 1 parameter

See example:
http://www.pc-prog.eu/phpBB3/images/xMyKB/VlookupSecond.xls

Palo
Palo-admin
Administrátor stránky
 
Príspevky: 473
Registrovaný: 18.07.2008, 16:43
Udelené poďakovania: 6 krát
Prijaté poďakovania: 63 krát

Späť na Solving MS Office problems in English

Kto je on-line

Užívatelia prezerajúci fórum: Žiadny registrovaný užívateľ nie je prítomný a 1 hosť