Vlookup on a cell with a formula

alexis.ray89371

Hi,

Is it not possible to use a vlookup formula on a cell that contains another formula? I tried and I get "no match".

This is the formula I tried. There is a match in my lookup table for Map ID 2. I have double checked. Thanks for any help!

Paul Newcome

My mistake. The JOIN is outputting text and you are searching for a numerical value.

=INDEX(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""), 1)

Andrée Starå

Hi @alexis.ray89371

Yes, that should work.

Can you describe your process in more detail and maybe share the formula(s), sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic day!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

alexis.ray89371

Hi!

Does this help?

Formulas below for copy/paste if needed. Image below with a little more explanation. Thanks!

=JOIN([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row)

=VLOOKUP([Outside Lots- Map ID]@row, {Landscaping- Outside Lots Lookup Table Range 1}, 2, false)

Paul Newcome

I think it has to do with using the JOIN function. Your JOIN function is basically outputting a string of "2blankblankblank", but the other sheet only has a "2". Try replacing the JOIN function with this:

=JOIN(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""))

Incorporating a COLLECT function and telling it to only JOIN those cells that are not blank should output "2" instead of "2blankblankblank".

alexis.ray89371

Hi!

I copied your formula in and I still get No Match

Paul Newcome

My mistake. The JOIN is outputting text and you are searching for a numerical value.

=INDEX(COLLECT([Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, [Outside Lots- Map ID for Zone 1]@row:[Outside Lots- Map ID for Zone 4]@row, @cell <> ""), 1)

alexis.ray89371

Hi!

I copied your formula in and I still get No Match

alexis.ray89371

It worked! Thank you!!

Paul Newcome

Happy to help. 👍️

Andrée Starå

@alexis.ray89371

Happy to help!

I saw that Paul answered already!

Let me know if I can help with anything else!

Best,

Andrée

alexis.ray89371

Will do! Thank you both. 😀