If cell A contains specific text and cell b contains specific text then return this value

Afternoon

If I have a cell A containing "Shop" and cell B containing "$9.99" I need it to look up a table that has numerous values

$9.99
$10.99
$15.99

and respond with a £ value

Shop $9.99 £8.99
Shop $10.99 £9.99
Shop $15.99 £14.99

Warehouse $9.99 £9.99
Warehouse $10.99 £10.99
Warehouse $15.99 £15.99

so

if Shop and $9.99 found then give me £8.99

but

if Warehouse and $9.99 give me £9.99

Gonna try myself but probably wont get there.

Hi Sorry

Dont think explain it fully, having issues posting and amending.

Shop $9.99 £8.99
Shop $10.99 £9.99
Shop $15.99 £14.99

Warehouse $9.99 £9.99
Warehouse $10.99 £10.99
Warehouse $15.99 £15.99

This part is in a table

So I would have one column thats says shop/warehouse...etc numerous different text and I have some tables that have shop, warehouse...etc with corresponding values.

So I need to look at said column and see if it has "Shop" or "Warehouse" or something else in my lookup table. And then look at another column that is the price then return the 3rd column from the lookup table with the correct price.

So if A1 and D1 are this then correct result is this

A1 Shop
D1 $9.99

in lookup table its

Shop $9.99 £8.99

So for every row that has shop,$9.99 in my new column it would say £8.99

This was if I change the price I can just change the £8.99 and its all automatic.

Really hope that makes sense.

Try

=IFERROR(INDEX($K$1:$K$100,MATCH(1,INDEX(($I$1:$I$100=A1)*($J$1:$J$100=D1),0),0)),"")

Table in Columns I to K

Ok have attached a spreadsheet

Sheet 1 Column B is type of product, Column J is the price

Sheet 2 has the lookup values and output is the column L

So when it says Previews Publications an $2.99 the value would be £2.99

Hi JetMc

Thanks for for formula it works like a charm, if I was to add another another column to look at say H, would that be like this

Formula:

Please Login or Register  to view this content.