# vlookup set of columns but return 2nd instance

#### davymcl

##### New Member
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>

### 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
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
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
 this is A1 check1 check2 check 3 a9 is offset start cell 19 13 99 12 10 9 8 7 5 col O row 19 4 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

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
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
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

M.

Replies
1
Views
47
Replies
5
Views
91
Replies
10
Views
170
Replies
4
Views
150
Replies
7
Views
228