VLOOKUP Help

[jmorovich]

We have an Excel sheet that is a bit complicated and the user who managed it left the company a couple weeks ago. The main issue I'm having with the document is the VLOOKUP formula used. This document helps us generate bid amounts so I do NOT want to take anything to chance when modifying it. I figured someone on this forum would look at this and have an answer in a second. Here is the formula:

=VLOOKUP(Recap!$A4,Data!$H$18:$I$47,2,FALSE)

Can someone give me some idea what this is doing?

Thanks in advance.

[Richard Buttrey]

Hi,

It's reading cell A4 on the Recap tab, then looking for that value in the range H18:H47 of the Data tab. When it finds the value in that column H range it looks across on the same row to the value in the 2nd column of the H18:I47 range, i.e. column I and returns whatever is in that cell.

HTH

[darkyam]

It's looking up the value in the Recap sheet, cell A4, then finding it in column H of the data sheet between H18 and H47. The 2 means that it's looking in the second column of the array H18:I47. False (or alternately, a 0) means that it has to find an exact match of A4 in column H. True (or 1) means that it finds the closest match without going over.

[martindwilson]

searches for the value given in sheet Recap! cell $A4
in column h (h18:h47)
of range Data!$H$18:$I$47 (worksheet data)

if an exact match is found (thats the FALSE bit) return the coresponding value in the same row from column 2 of the range Data!$H$18:$I$47 i.e column I
snap essentially the same as above!!!

[tcowen]

You will also want to note that the range

Data!$H$18:$I$47

is fixed, it won't vary. So, if you ever add to that set of data, you will need to adjust this part of the formula.

I prefer dynamic named ranges.