Solved: Excel 2007 match column A & B to table and get value C

You need to lookup the value which is associated with both location codes. I've used an array formula which allows you to take the lookup value (combination of the two location codes - GH MM) and search for a match when the distance sheet shows that combination. A normal formula won't allow you to string together the distance sheets columns. The IF part checks to see if the two codes are the same and returns 0 miles if they are, then the iferror parts say look for GH MM, if no match, try MM GH and if no match, say "No Match".

Array formulas must be entered by typing the formula and then using ctrl-shift-enter. However, as with other formula types you can drag the formula down to fill new cells and rows as normal.

That works great. I have one more question. If by chance they don't enter a second site, I get no match, of course, but wanted to find out if the user doesn't have a second site to enter for the day how can i make the cell C blank instead of No Match

A quick way to do that is to select the formula column and do a find and replace



Find "No Match"

Replace with ""

I did that but was wanting to be able to keep the No Match if not on the list. No biggie though. thank you

I just added a further check to see if there was only one code input rather than two - this retains the No Match option. Hope this helps.

Awesome. You rock!