VLOOKUP returns #N/A when lookup value is an output of a formula

[Bunnifer]

Hi all,

I am attempting to do a simple VLOOKUP but can't seem to get it to work when my lookup value is a result of a formula. I also probably have done a complicated way of searching things up but if you guys can have a look please let me know if there are any ways to do this better.

Essentially, I am trying to create a table showing what works are suitable to implement together. Unfortunately, I can't download XL2BB as it is a company laptop which restricts what I can install and use. I have uploaded an image I hope it helps.

Highlighted yellow is user's input. Blue underline is output of an ID of the road I'm looking at. Detours underlined in red are returning #N/A and I'm not sure why, but I want it to spit out the corresponding detours I have in the table.

The code I have in the "Ramp Number" is as follows, wanting to check the Road input, NB or SB input and To or From Tonkin input to return the appropriate ID under Ramp Number.
=INDEX('Tonkin Hwy Ramps DO NOT CHANGE'!D:D,MATCH(1,('Tonkin Hwy Ramps DO NOT CHANGE'!A:A='Ramp Compatibility Checker (1)'!$C$3)*('Tonkin Hwy Ramps DO NOT CHANGE'!B:B='Ramp Compatibility Checker (1)'!$D$3)*('Tonkin Hwy Ramps DO NOT CHANGE'!C:C='Ramp Compatibility Checker (1)'!$E$3),0))

Then:

I want the VLOOKUP to look at that ID that has been spat out and return the associated detours per the table image I uploaded.
=VLOOKUP($F$3,Ramp_Table,5,0) for the Traffic Detour

=VLOOKUP($F$3,Ramp_Table,6,0) for the HVS Detour

=VLOOKUP($F$3,Ramp_Table,7,0) for the Alternate Detours

Any input or suggestions is really appreciated!

Kind regards,

Jennifer La

 

[Peter_SSs]

Welcome to the MrExcel board!

Is Ramp_Table a formal Excel table (ListObject) or a normal range that you have named?

Assuming it is a formal table try this for the Traffic Detour, and change that column label for the others

=XLOOKUP(F3,Ramp_Table[Ramp No.],Ramp_Table[Traffic Detour])

 

[Bunnifer]

Welcome to the MrExcel board!

Is Ramp_Table a formal Excel table (ListObject) or a normal range that you have named?

Assuming it is a formal table try this for the Traffic Detour, and change that column label for the others

=XLOOKUP(F3,Ramp_Table[Ramp No.],Ramp_Table[Traffic Detour])

That worked, thank you! I understand what went wrong now. Totally forgot about the XLOOKUP function!

Kind regards,

Jennifer La.

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.