If, Vlookup and Concatenate all together in one formula?

[TotallyLost]

I'm trying to write a formula that will use vlookup and concatenate but look across two columns for the answer. If there is no answer in the first of the two columns then use the number in the second of the two columns.

Attached is an example that should help. Please note that if, vlookup and concatenate may not be the best way to go about this, but in my beginner mode that's all I know.

The attached is only showing one state, but there are many states so that is the reason for the vlookup.

Thank you. This website and it's contributors have helped me tremendously in my job.

[Chris 53]

In Sheet 'DSS' B3 formula enter =C3&E3 and copy it down.
This gives the Vlookup something to look for,

Chris

[ragulduy]

=INDEX(DSS!F$1:F$4,MAX(IF(DSS!C$3:C$4=C4,ROW(C$3:C$4))*IF(DSS!E$3:E$4=Tracker!E3,1,0)))

maybe try that in E4 of your tracker sheet.

It's an array formula so needs to be confirmed with ctrl+shift+enter.

[ARGK]

Hi
See solution attached (hope I understood you correctly).
Good luck.
Tony

[amit.wilson]

Hello Totally,

am not sure concatenate would work here.

Try this in D4:

Please Login or Register  to view this content.

and in F4:

Please Login or Register  to view this content.

I've used the max function assuming that one of the columns (F&G on DSS) will always be blank or zero.

And that the names in column C are unique, i.e. no repeats.

Cheers

[TotallyLost]

Thank you all so much. This was very helpful and answered my question.