multi cell vlookup

I think you need to supply a sample file (without sensitive data) showing how your data is arranged.

Include an explanation (with examples) of what results you want and the logic behind achieving those results.

you are not mentioned here the result range,
Try this formula

=Match(LookupValue1&LookupValue2,LookupRange1&LookupRange2,0)

this will return row number which matches the criteria.
Hope this will do.

I would think it depends on how the relationships between column J and L work. However, taking a bit of a guess at how your sheet may be structured:

Example 1: if there is 1 to 1 link between column J and column L

If the value of L is driven directly by J (for example if J is 'tourist' L has to be a set amount which will be the same every time J is 'tourist') then you can drive the value of L from either IF statements, e.g. in L2:

IF(J2="Tourist"),"Value for tourist",nestle further IF statements here)

or you could do a small VLOOKUP to a table with the two columns for values of J and corresponding values for L
Either of the above two could then be used to drive your formula

If there is not a 1 to 1 relationship between J and L
There are a number of questions that would need to be answered to decide the best route. 2 options would be. As I don't know what each column is for I will use if J was a column for 'type of resident', e.g. tourist and L was a column for 'type of room' and M was a column for 'price to charge'

Example 2: There is a fixed relationship between J and L that calculates M

I guess that it is not this as it is quite simple, a simple formula such as

IF(J2="Tourist"),50,if(J2="Resident",40,100)) + If(L2="luxury",25,10)

would charge a total rate dependent on the type of resident (£50 for a tourist, £40 for a resident, £100 if anything else) plus the room type (a further £25 for a luxury room and £10 for non luxury)

Example 3: If there is more of an arbitrary decision that determines the value of M (or it cannot be calculated)

Lets say it is the same example as above but an arbitrary decision has been made on the total cost for each situation then you could put these figures into a table (say with the x axis being type of resident and y axis being type of room.

You can nestle a MATCH formula in your VLOOKUP, so you run a VLOOKUP as normal to find the correct row for the type of resident. The column_index_num section of the VLOOKUP is then a MATCH formula:

MATCH(lookup_value,lookup_array,match_type)

The full formula being:

VLOOKUP(J2,table_array,MATCH(L2,lookup_array,0),FALSE)

where table_array is the entire table you created for the values of M and lookup_array in the header row of that table.

Thanks for your help so far.

I attach a sample workbook.

The after sheet has been filled in with examples of cost for column headed Agent Fee.

This works a treat.

Can you please tell me what the particular functions do (only way I will understand and learn).

Also if I enter more columns in the future for additional agents on sheet COST will your solution still work?

Thank you for your help so far.

Thank you for your invaluable help.