VLOOKUP HELP

[dwu]

I need to put together a spreadsheet like the following:

Freq/Mhz MER BER pre BER post P/F
537
543
591
597
603

I want to put the VLOOKUP command in the P/F column, so
when I input a level of 30 on the MER column, the P/F
column will show Marginal and so on.

MER

35 Excellent

31-34 Good
28-30 Marginal
<28 Fail

 

[Guest]

Let's say the MER header is in cell B1 and its 4 values
are in B2 to B5. In P/F column, row 2 I would write:

=IF(A2>=35,"Excellent",IF(AND(34>=A2,A2>=31),"Good",IF(AND
(30>=A2,A2>=28),"Marginal",IF(A2<28,"fail","error"))))

If you find a Vlookup method let me know--I only have
used Vlookups with the "False" option at the end, looking
for exact matches. Not sure how it handles borderline
situations with the "true" option.

 

[Pete]

sent a reply just now without my name so I'm not sure it
will post. Here goes again--the formula I'd use in the
MER column is this--I tested it and it works:

=IF(A2>=35,"Excellent",IF(AND(34>=A2,A2>=31),"Good",IF(AND
(30>=A2,A2>=28),"Marginal",IF(A2<28,"fail","error"))))

I'd be interested if you find a Vlookup to do this, as
I've only used Vlookups with the "False" option at the
end, to test for exact matches rather than ranges.

Regards,
Peter

 

[Domenic]

First, set-up a lookup table on Sheet2 in Columns A and B, something
like this...

0 Fall
28 Marginal
31 Good
35 Excellent

Then, on Sheet1, enter the following formula in E2 and copy down:

=VLOOKUP(B2,Sheet2!$A$1:$B$4,2,1)

I'm assuming that you have five columns, and that Column B is your MER
column. It's a little difficult to tell from what I see.

Hope this helps!