vlookup set of columns but return 2nd instance

davymcl

New Member
Joined
Mar 14, 2019
Messages
3
Hi all,

I found a solution to my 1st problem via this old threadhttps://www.mrexcel.com/forum/excel-questions/775611-if-vlookup-blank-one-column-then-vlookup-another-column.html

My resultant formula that works is "=IFNA(IF(A11="","",LOOKUP(99^99,VLOOKUP(A11,'DDL SW'!G:AQ,{21,23,29,32,37},FALSE))),"")"

My next problem is that I want the formula to look through the columns 37,32,29,23,21 and when it finds an entry (a date in my case) to return the value of the next column. I.e. if 37 is blank, check 32, if 32 is blank, check 29, if there is an entry in 29 then return the value of 23.

I doubt there is a way of doing this without seriously changing up the formula but I thought i'd ask.

Thanks,
David

<strike></strike>
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Welcome to Mr Excel

See if this does what you need
=IFERROR(IF(A11="","",LOOKUP(1000,VLOOKUP(A11,'DDL SW'!G:AQ,{21,23,29,32,37},0),VLOOKUP(A11,'DDL SW'!G:AQ,{21,21,23,29,32},0))),"")

Question:
What result do you want when only the first column of the array (column 21) contains a number?
I assumed you want the result in column 21 - adjust to suit

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
oops...

Try
=IFERROR(IF(A11="","",LOOKUP(99^99,VLOOKUP(A11,'DDL SW'!G:AQ,{21,23,29,32,37},0),VLOOKUP(A11,'DDL SW'!G:AQ,{21,21,23,29,32},0))),"")

M.
 
Last edited:

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
this is A1
check1check2check 3
a9 is offset start cell19139912109875
col O
row 194
9
12
99
99 obtained by
=IF(OFFSET($A$9,0,O19)<>"",OFFSET($A$9,0,O19+1),IF(OFFSET($A$9,0,O20)<>"",OFFSET($A$19,0,O20+1),IF(OFFSET($A$19,0,O21<>"",OFFSET($A$19,0,O21+1),"no match"),"no match")))

<colgroup><col><col span="21"></colgroup><tbody>
</tbody>
 

davymcl

New Member
Joined
Mar 14, 2019
Messages
3

ADVERTISEMENT

Hi Marcelo,

Thanks for the welcome and your excellent solution! With the IFERROR at the start it was yielding blank results but once I switched that part back to IFNA it worked perfectly! Thank you very much. You assumed correctly with column 21 as well, cheers. Hopefully I can give back to the community when I get better.

David
 

davymcl

New Member
Joined
Mar 14, 2019
Messages
3
Hi Marcelo,

Yes my mistake, the IFERROR was not the issue, it was the 99^99, thanks again. Thanks for your time too oldbrewer, I will stick with Marcelo's answer however.

David
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,393
Hi Marcelo,

Thanks for the welcome and your excellent solution! With the IFERROR at the start it was yielding blank results but once I switched that part back to IFNA it worked perfectly! Thank you very much. You assumed correctly with column 21 as well, cheers. Hopefully I can give back to the community when I get better.

David

You're welcome. Glad to help.

M.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,965
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top