# finding a value in a range

#### Stubby

##### Board Regular
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.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Domski

##### Well-known Member
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

##### Well-known Member
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

##### Board Regular
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.

#### Richard Schollar

##### MrExcel MVP
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

##### MrExcel MVP
Hi Stubby

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

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

#### Stubby

##### Board Regular
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

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

#### Ghost666th

##### New Member
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.

1,190,948
Messages
5,983,808
Members
439,862
Latest member
FaisalAlTawil

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back