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>
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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.
 
Upvote 0
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:
Upvote 0
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>
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top