Multiple Criteria VLOOKUP Formula

[Rob0424]

I have a VLOOKUP formula that currently works:

=VLOOKUP(C2,'2023 RATES'!$A$1:$F$2000,5)

The problem I am trying to solve is if this formula does not find what it is looking for it returns #N/A. Is there a way to add to this formula that if it does not find anything then it would move onto this formula:

=VLOOKUP(D2,'2023 RATES'!$A$1:$F$2000,4)

And then the problem with adding this is there could be multiple times this is found, so it should stop at the first one found.

Can anyone help me?

 

[Rob0424]

211172 Monthly Equipment Reconciles.xlsx
	A	B	C	D	E
1	Job Number	Month	Equipment Number	Equipment Description	FHWA Monthly Rate
2	211172	February 2024	153-02976	Takeuchi TL10CR	$ 10,466.82
2024 - Mar (TEST)

Cell Formulas
Range	Formula
E2	E2	=IFNA(VLOOKUP(C2,'2023 RATES'!$A$1:$F$2000,5,0),VLOOKUP(D2,'2023 RATES'!$A$1:$F$2000,4,0))

 

[Rob0424]

153-00001	Takeuchi TL12V2-CRH	$2,500.00​	$7,754.62​	$10,466.82​
153-00002	Bobcat T740 Track Load	$2,500.00​	$6,513.75​	$8,964.45​
153-00003	Bobcat T750 TrackLoade	$2,500.00​	$6,513.75​	$8,964.45​
153-00004	Takeuchi TL 12V2-CRHR	$2,500.00​	$7,754.62​	$10,466.82​
153-00005	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-00006	CAT 299D Skid Steer	$2,500.00​	$7,754.62​	$10,466.82​
153-00007	Takeuchi TL 12	$2,500.00​	$7,754.62​	$10,466.82​
153-00008	Takeuchi TL 12	$2,500.00​	$7,754.62​	$10,466.82​
153-00009	20 CAT 299D3 Skid Stee	$2,500.00​	$7,754.62​	$10,466.82​
153-0000B	Skid Steer Bucket and	$2,500.00​	$7,754.62​	$10,466.82​
153-00010	20 CAT 299D3 Skid Stee	$2,500.00​	$7,754.62​	$10,466.82​
153-00011	20 CAT 299D3 High Flow	$2,500.00​	$7,754.62​	$10,466.82​
153-00012	21 CAT 289D3	$2,500.00​	$7,754.62​	$10,466.82​
153-02960	Takeuchi TL240CR	$2,500.00​	$7,754.62​	$10,466.82​
153-02967	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-02970	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-02976	Takeuchi TL10CR	$2,500.00​	$7,754.62​	$10,466.82​
153-02982	Takeuchi TL8	$2,500.00​	$7,754.62​	$10,466.82​
153-02983	Takeuchi TL8	$2,500.00​	$7,754.62​	$10,466.82​
153-02984	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-02985	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-02986	Takeuchi TL10	$2,500.00​	$7,754.62​	$10,466.82​
153-02987	Takeuchi TL12V	$2,500.00​	$7,754.62​	$10,466.82​
153-02988	Takeuchi TL12V	$2,500.00​	$7,754.62​	$10,466.82​

 

[Fluff]

The value in col D is not in col A of the rates sheets, hence why it doesn't work.
Try

=IFNA(VLOOKUP(C2,'2023 RATES'!$A$1:$F$2000,5,0),VLOOKUP(D2,'2023 RATES'!$B$1:$F$2000,4,0))

 

[Rob0424]

That seems to have fixed it. Thank you so much for your help

 

[Fluff]

You're welcome & thanks for the feedback.

 

[bertible]

OK, but I tried copying the text from cell D2 into the cell on the '2023 Rates' tab so they should match exactly. Any idea why it might not be recognized?

I use external data sources from our business system for some of my reference sheets. When using that data for VLookup (Xlookup, etc.), these problems often occur due to the inherent cell formats built into that imported data. Copying a Text or Value from another sheet like you tried sometimes works, if you reformat the target cell first (i.e. Text, General, Number) but it can be very frustrating to figure this one out. I've found that using Text and Value functions within your Lookup formula is the best solution if the external table is your cause. (in my case, the offending data fields are usually part numbers, which appear numerical but are actually text.)