Need help with a VLOOKUP formule with two criteria...

Hi everyone,

i need some help with returning a value in a cell (or returning several values) based on not one but multiple criteria.

For example, column B always has a two to three letter code that is basically one or two letters and zero or one symbol (only + or -), like 'CV-' or 'AD+' or 'R-'. Column E includes string that can include the two letter code 'FA', amongst other string values, or even nothing.

Please Login or Register  to view this content.

Now, what I'm looking for is, a formule that says to look up a certain string in column B, let's say for this example string 'D+', and another string value in column E, let's say 'fa', and then return the matching string value in column C or D or whatever column.

I know how to write the formula to lookup 'D+' in column B and return the column C value (but then it returns the second row value from column C, not incorrect but the fifth row also matches), and I know how to write the code to lookup 'fa' in column E and return the column C value (but then it returns the third row value from column C, not incorrect but the fifth and sixth row also match), but I don't know how to write the formula to return the only string value (or the first of all string values) in column C for which both the column B value is 'D+' and the column E value contains 'fa'. Any help?

Denis

you can use an array formula
=INDEX(C:C,MATCH(G5&H5,B:B&E:E,0))
use control + shift+ enter for an array
where G5 & H5 have the lookup criteria

although I suspect you are after more than a simple lookup

not sure what you are trying to do - not clear from the spreadsheet or rows
The relevant data is in row 2555 to row 2809.


you will need to explain exactly what you are doing and examples of the data and result

the formula will work - BUT as i say, not sure that was what you were after

this may be the issue
B2555:C2809&L2555:O2809
should it be
B2555:B2809&L2555:L2809

breaking it down
you have merged columns, never good for formulas to work in

P11 = D+
P12 = FA

=INDEX(D2555:D2809,MATCH(P11&P12,B2555:B2809&L2555:L2809,0))

you want to return value in D+ that matches in column B and FA in column L

which row has
D+ in column B
and FA in column L

can you post which row has just D+ in column B and JUST FA in column L
I think this is where the string comments may have been and so its NOT just those values
if it were , then the formula i posted would work

you are welcome