finding a value in a range

[Stubby]

Hi

I have some data set out as follows:

BB-7865 $54.99 BB-7866 $54.99 dB-7865 $76.00
EM-3741 $125.99 EM-3742 $55.99 dM-3741 $77.00
GR-0876 $99.99 GR-0878 $56.99 dR-0876 $78.00
JH-0678 $33.98 JH-0679 $57.99 dH-0678 $79.00
ST-2472 $63.99 ST-2473 $58.99 dT-2472 $80.00
TY-9868 $169.99 TY-9866 $59.99 dY-9868 $81.00
WE-5493 $44.99 WE-5491 $60.99 dE-5493 $82.00
ST-2471 $54.75 ST-2477 $61.99 dT-2471 $83.00

I have another cell with a value ie ST-2473

is there any function (non VBA) that will allow me to search all of the above and then return the correct price for ST-2473.

 

[Domski]

You could nest a load of VLOOKUP and ISNA(VLOOKUP) formula but it would be a pretty messy formula. Is it not possible to arrange the data in one list?

Dom

 

[Domski]

Example of formula if you're interested:

=IF(AND(ISNA(VLOOKUP(A11,$A$1:$B$8,2,FALSE)),ISNA(VLOOKUP(A11,$C$1:$D$8,2,FALSE))),VLOOKUP(A11,$E$1:$F$8,2,FALSE),IF(AND(ISNA(VLOOKUP(A11,$A$1:$B$8,2,FALSE)),ISNA(VLOOKUP(A11,$E$1:$F$8,2,FALSE))),VLOOKUP(A11,$C$1:$D$8,2,FALSE),VLOOKUP(A11,$A$1:$B$8,2,FALSE)))

Assuming your data is in A1:F8 and the lookup value in A11.

Dom

 

[Stubby]

Hi Domski

Unfortunately the data can't be rearranged! however i did use the ISNA together with a vlookup, if and NOT function ( IF(NOT(ISNA(VLOOKUP ) and it appears to work! but it is very messy.

Thanks for the advice

 

[Richard Schollar]

Hi

Assuming your values are unique (ie ST-2473 doesn't appear anywhere else in the table) then you could just use SUMIFs:

=SUMIF(A:A,"ST-2473",B:B)+SUMIF(C:C,"ST-2473",D:D)+SUMIF(E:E,"ST-2473",F:F)

 

[pgc01]

Hi Stubby

Assuming the code unique, the data in A1:F8 and the code in H1, try:

=SUMPRODUCT(--(A1:E8=H1),B1:F8)

 

[Stubby]

Hi

Assuming your values are unique (ie ST-2473 doesn't appear anywhere else in the table) then you could just use SUMIFs:

=SUMIF(A:A,"ST-2473",B:B)+SUMIF(C:C,"ST-2473",D:D)+SUMIF(E:E,"ST-2473",F:F)

Many thanks I will give it a try

 

[Richard Schollar]

I'd go with PGC's if I were you - it was a lot more succinct than mine!

 

[Ghost666th]

Big thanks to Stubby for posting the question and to ALL for your help.
Taken two hours of work a day away from me with this formula, if i can get it 100% perfect with other variables needed, i could go home before leaving for work
Again, THANK YOU to ALL.