VLOOKUp Help!

lauratmc

Hi there,

I am trying to return a value (site ranking) from sheet 1 to sheet 2 where a value (site #) matches that in my second sheet, but I can't see to get it to work.

VLOOKUP.docx

I have tried:

=VLOOKUP([Site #]@row, {FL-101 Feasibility Questionnaire Raw Data Range 3}, 3)

Any ideas?

Thanks!

Mark Cronk

Hi @lauratmc ,

VLOOKUP looks for a match in the left most column of the lookup range. Confirm that [Site No] is the left most column in {FL-101 Feasibility Questionnaire Raw Data Range 3}. I suspect it's not because you're returning column 3. You should be returning column 2 since [Site Ranking] is the column next to [Site No].

Work?

Good luck!

Mark

lauratmc

VLOOKUP2.docx

Hi Mark!

The formula now looks like this:

=VLOOKUP([Site #]@row, {FL-101 Feasibility Questionnaire Raw Data Range 1}, 2, false)

But the cell turns blank - I know that these sites are listing in the lookup table. The third one is not in the lookup table - how do I get it to be blank if not found?

The "Site #" column is left-most in the range.

Thanks!

lauratmc

Oh I've just realised - it's because the 'Site Ranking' is actually derived from a formula! Is there a way for it to just list what the cell says?

Mark Cronk

Good morning Laura,

VLOOKUP's return a value so having a formula in the Site Value of the lookup table should still work. If the Site # isn't found in the lookup table you'll get a #No Match error.

I think I see your problem. There is a drop down icon on your Site Ranking column on the Sheet. Try changing the column property to Text/Number and see if you get the response you're looking for.

Also, convert the Site Ranking formula on the Sheet to a Column Formula.

To avoid the #No Match error, start your formula with IFERROR: =IFERROR(VLOOKUP([Site #]@row, {FL-101 Feasibility Questionnaire Raw Data Range 1}, 2, false),"Not Found")

If you want the Site Ranking column on the sheet to be a symbol you need to match the Site Ranking value on your lookup sheet to the symbol values.

Hope this works!

Mark