# Lookup values from multiple columns

Hi,

I want to look up values in the blue boxes in the attached sheet.

Items are sold to three market types - under two different price settings. How to look up say for a specific item [sk1] so that both columns under a market type is shown.

Like look up (sk1) returns only prices for a specific market. I tried to create named ranges but how this could be achieved?
Invoice table will show the two price sets :( p1 and p2) for only those items that corresponds to a specific item and a specific market type.

Example:
invoice2      sk1      conventional market       \$    45.00        \$    50.00

What formula can be put in those two blue boxes to do this?
multiple-column-look-up.xlsx
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mechanical EngineerCommented:
The exact formula required will depend on your worksheet layout. For the sample workbook, I used:
=VLOOKUP(\$A4,\$F\$5:\$L\$1000,MATCH(\$B4,\$G\$3:\$L\$3,0)+1,FALSE)      for cell C4. Returns the P1 price for a SKU in A4 and market in B4
=VLOOKUP(\$A4,\$F\$5:\$L\$1000,MATCH(\$B4,\$G\$3:\$L\$3,0)+2,FALSE)      for cell C4. Returns the P2 price for a SKU in A4 and market in B4. Note the +2 in formula.
multiple-column-look-upQ27656390.xlsx

Experts Exchange Solution brought to you by