Forum Discussion
Multiple Formula Errors - IF VLOOKUP and CONCATENATE
=IF(ISBLANK(G10)," ",(VLOOKUP(CONCATENATE(F10&G10),(Lists!$C$2:$E$2),4,FALSE))) - located in H10
The formula above is what I am using to auto-populate responses for a risk register.
F10 - has 6 options
G10 - has 6 options
These two cells are populated from lists in C and E 1-7 (including title) in a different worksheet. The drop-down menus are functioning as they should.
H10 should auto-complete based on the choices made in F10 and G10.
(EG when a person makes the choice 5 severe in F10 and D unlikely in G10, H10 should auto-fill to High and I11 to 10).
I have been at this for hours now and each chunk of the formula seems ok on its own but altogether the formula does not work.
The formula has colours highlighting errors but I am unsure how to fix them.
Please help.
4 Replies
sooxan First of all, your formula contains some elements that not needed. It could be re-written like this:
=IF(ISBLANK(G10)," ",VLOOKUP(F10&G10,Lists!$C$2:$E$2,4,FALSE))But, this will also return an error. You look up the combined value of F10 and G10. VLOOKUP expects to find this value in the first column of the lookup range ($C$2:$E$2, i.e. 1 row, 3 columns) and then you want it to return the 4th column. That's impossible as there are only 3 columns in the lookup range. Hence, an error.
Perhaps your lookup range is $C$2:$E$9 with headers in the top row. Then you would need HLOOKUP and the formula could be:
=IF(ISBLANK(G10)," ",HLOOKUP(F10&G10,Lists!$C$2:$E$9,4,FALSE))=IF(AND(ISBLANK(F10),ISBLANK(G10)),"",VLOOKUP(F10&G10,Lists!$A$10:$B$45,2,0))
The above formula is working!! Success.
BUT
Now I need to get the column next to it to autofill a number based on the result of the above formula.
(note - the issues was my lists, as well as the formula)
sooxan glad you worked it out!
In your follow-up question you say:
"Now I need to get the column next to it to autofill a number based on the result of the above formula."
That's a bit vague. Can you explain what the VLOOKUP formula results in and what rules apply to come the "a number". Best to upload your workbook, freed from confidential information, indicating what should go where on what basis.
If I may suggest, please add a file (without sensitive data) and describe your plan on it. The error could be identified faster and more specifically. Please no picture, even if it is said that a picture is worth a thousand words, it is definitely not in the case of Excel.
This means that you can be helped more quickly and the best possible solution tailored to your needs can be offered. So everyone is helped.
Help to be helped.
* It's always good to add your operating system and Excel version.
Thank you for your understanding and patience.
Nikolino
I know I don't know anything (Socrates)
