formula help

Topic

New Reply

Hello

I would be very grateful if anyone could shed some light on where i am erring with the formula below
=IF(A2=1,G2,vlookup,Sheet2!C10:d12C10:D12,Sheet2!F10:G12))
Can somebody please help with the above formula. I am trying to get the program to look and see if the value in a2=1 or a2=2 then lookup the value of g2 using either one of two look up tables all depending if a2=1 or a2=2.

Much Thanks for any advice
Graham

Reply | Quote

Replies

Hi Graham,

If I am reading you right the following formula should do it :

=IF(A2=1,VLOOKUP(G2,Sheet2!C10:D12,2,FALSE),IF(A2=2,VLOOKUP(G2,Sheet2!F10:G12,2,FALSE),0))

If Cell A2 will always be either 1 or 2, you could change it to :

=IF(A2=1,VLOOKUP(G2,Sheet2!C10:D12,2,FALSE),VLOOKUP(G2,Sheet2!F10:G12,2,FALSE))

If you are looking for the nearest value to G2 instead of an exact match, you can omit the ,FALSE in all the above. I am assuming you are looking for the value in the second column of each lookup range.

A good idea might be to name those ranges say Table1 and Table2, so instead of entering C10:D12 you would enter Table1 in the above formulas.

The Vlookup formula works as follows :

VLOOKUP(what you want to look for, the range where you want to look, and the column of that area you want the value from, and then if you want an exact match you enter ,FALSE).
The value you are looking for (G2) must be in the first (leftmost) column of the area you are looking up (C10:G12). If you look for an exact match and it cannot be found, you will get #N/A error.

Hope I understood your problem and you can follow the above.

Regards,

Andrew C

Reply | Quote

comic]]The problem is that you have a comma after vlookup instead of enclosing it all in parentheses. It should be:

=IF(A2=1,VLOOKUP(G2,SHEET2!C10:D12,2), VLOOKUP(G2, SHEET2!F10:G12,2))

I would also suggest naming your lookup ranges. This will make the function much easier to work with. My above function says: “If A1=1 the go to Sheet 2 to the lookup table contained in cells C10:D12 and look for the corresponding information in the second column. If A1 does not equal 1 then go to Sheet 2 to the look up table contained in Cells F10:G12 and look for the corresponding information in the second column. I hope this is helpful!]

Reply | Quote

Hi Guys
Thanks very much for the assistance once again. i am very grateful for the assistance and knowledge I am recieving from this site.

Graham

Reply | Quote