Excel Formula To LookUp

0
votes

I have two sheets. Sheet1 has BrandName and the Price.
Sheet2 sheet has same info, BrandName and Price. But this sheet has multiple Prices for same Brand.
I want a formula to lookup the closest price for a brand from Sheet2 to Sheet1.
Very Much similar to This

Sheet1
Brand-------Price------Closest Price
A--------------100 --------?
B--------------200---------?

Sheet2
Brand-------Price
A--------------100
A--------------105
B--------------201
B--------------210
B--------------205

1
excel-formula

1 Answers

1
votes

The formula in your Sheet1!C2 could be

{=INDEX(Sheet2!B:B,MIN(IF(IF(Sheet2!A:A=A2,ABS(Sheet2!B:B-B2))=MIN(IF(Sheet2!A:A=A2,ABS(Sheet2!B:B-B2))),ROW(Sheet2!A:A))))}

This is an array formula. Input the formula into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to finish.

Array formulas are poor in performance. So if it is slow in calculation, shrink the ranges related to Sheet2. Try Sheet2!$A$1:$A$10000 and Sheet2!$B$1:$B$10000 for example.