Comparing Two Vlookups to Render a Result

[Kogen]

Vlookup Problem.png

I'm having problems using an IF function to render results with two vlookups. I need to know that, if the "actual family size" and the "total gross" are greater than the table to the left, then the result needs to be "Not Eligible"; otherwise, "Eligible". I have been having a heck of a time nesting formulas; can anyone help?

[jtakw]

Hi,

Try this:

Excel 2016 (Windows) 64 bit

	A	B	C	D	E
2	1	12140		6	24960
3	2	16460
4	3	20859
5	4	25749		Eligible
6	5	30392
7	6	35542

Sheet: Sheet55

Excel 2016 (Windows) 64 bit

	D
5	=IF(VLOOKUP(D2,A2:B7,2,0)<E2,"Not Eligible","Eligible")

Sheet: Sheet55

[Sam Capricci]

I would think this would work but I'm not sure of your actual ranges so it is based on a few guesses...
=IF(VLOOKUP(I4,H6:I6,2,FALSE)>VLOOKUP(I4,D6:E11,2,FALSE),"Not Eligible","Eligible")

[Kogen]

Vlookup Problem.png

Jtakw,
I got a #N/A result. Did I transcribe your formula suggestion correctly?

[Sam Capricci]

now that I see your actual ranges here is the adjustment to my formula with what I think you want...
=IF(VLOOKUP(H34,G37:H37,2,FALSE)>VLOOKUP(H34,B37:C42,2,FALSE),"Not Eligible","Eligible")

[Kogen]

It didn't work. Just to clarify, I have G37 = H34 and H37 = H31 so that I could create a Vlookup option. This excel sheet will be used to determine eligibility for a program for many people; so the fields will be changing with each new client.

[Sam Capricci]

not sure what isn't working so I am posting a sample with your notes and the results.
I am still using the formula I gave you in post #5.
perhaps you can clarify why it isn't working.

[jtakw]

Attachment 588531

Jtakw,
I got a #N/A result. Did I transcribe your formula suggestion correctly?

Hard to say, looks like you have Merged cells, so I can't be absolutely sure the cell references you're using are correct.
May be try changing B37:E42 to B37:C42

But my suspicion is that you may have Text values in the Table (B37:E42) and/or G37, H37

You can check those values, in an unused cell, enter =ISNUMBER(G37)
Do the same will the other cells in question.

[Kogen]

Firstly, thank you for taking the time with me Sambo Kid & Jtakw. F37 & G37 were merged cells and when I un-merged them the formula ran. However, I'm running a number that should be beneath the threshold and it's saying "not eligible". It's frustrating not knowing why something is going wrong... I've attached my excel sheet to show work.

Excel Forum Example.xlsx

[Sam Capricci]

change your formula to this...
=IF(VLOOKUP(H34,G37:H37,2,FALSE)>VLOOKUP(H34,B37:D42,3,FALSE),"Not Eligible","Eligible")
it is a problem with the merged cells.

[Kogen]

It worked!

[Sam Capricci]

good, glad we could help you! AND thank you for the rep!

[jtakw]

Using my formula from Post #2 adjusting cell/range references according to your uploaded sample, shows correct results.

See attached.

[congnt92]

Or by simple index
=IF(INDEX($D$37:$D$42,H34)<H37,"NE","E")