VLOOKUP evaluating 2 columns

ddub25

Well-known Member
Joined
Jan 11, 2007
Messages
625
Office Version
  1. 2019
Platform
  1. Windows
I've been trying to get a VLOOKUP to look at text in Column B and in Column I (in the same row) on one sheet and then find the exact same text values held in Column BV and Column BW (in another workbook called 'REF' and on a sheet within the workbook called 'ref1'). When it finds these values, I want it to return the text held in Column BX.

I've been struggling to get the correct syntax to incorporate the 2 initial column references. Can anyone help.

Dan
 
You can do it with sumproduct, but it is not advised, but it will work as long as each combination is unique and you don't need to use CSE:

=INDEX('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130,SUMPRODUCT(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811),--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811),row($A$5:$A$1130)))

But I would still use Jonmo's or Macelo's approach.
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Is it possible to have the formula return '0' if it does not find a match? At present it returns '#N/A'.

Dan
 
Upvote 0
My two penneth for what it's worth...

For single (unique) value retrieval, INDEX and MATCH (with IF if two lookups are required) is my favoured approach.

SUMPRODUCT (or SUM(IF for that matter) should only really be used if the lookup combination is not unqiue and one is wanting to get a count of those combinations, or there are numeric values linked to the combination that one is wanting to sum.

At least that's the way I see it...

Matty
 
Upvote 0
Is it possible to have the formula return '0' if it does not find a match? At present it returns '#N/A'.

Dan

What version of Excel are you using?

Matty
 
Upvote 0
To elaborate further - it returns '0' if B811 and I811 are empty, but returns '#N/A' if they are not empty.

Dan
 
Upvote 0
I am 95% sure I am using Excel 2007. The modern office layout is confusing, can never find the 'About' section.

Dan
 
Upvote 0
If you have Excel 2007, try:

=IFERROR(Marcelo's formula here,"0")

If this doesn't work, then you don't have Excel 2007 and you would need to employ:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"0",Marcelo's formula here))

Matty
 
Upvote 0
Thanks Matty, you've been a great help. I must have Excel 2007 as the first formula worked straight off. Thanks.

Dan
 
Upvote 0
If you have Excel 2007, try:

=IFERROR(Marcelo's formula here,"0")

If this doesn't work, then you don't have Excel 2007 and you would need to employ:

=LOOKUP(REPT("z",255),CHOOSE({1,2},"0",Marcelo's formula here))

Matty

Matty,

This a very good way to use "IFERROR-kind" function in 2003 or ealier versions.

If i inderstood correctly, if the formulas would return a number it would be

=LOOKUP(9,999E+307,CHOOSE({1,2},"0",Marcelo's formula here))

Am i right?

M.
 
Upvote 0
Hi Marcelo,

Yes, if the value being retrieved is numeric, then it would be:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"0",formula))

It avoids a construct like:

=IF(ISNA(formula),"0",formula)

Where Excel has to evaluate the same formula twice, which, if the formula was quite "resource hungry", could slow your Workbook down.

Hope this helps.

Matty
 
Upvote 0

Forum statistics

Threads
1,225,149
Messages
6,183,194
Members
453,151
Latest member
Lizamaison

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