What is the proper formula to use for this?

htchandler

New Member
Joined
Apr 17, 2011
Messages
47
I'm trying to do a V lookup, the problem is that for each lookup value, there are three colums it will have to look at for a result. Only one of those three colums will have a result, the other two (for each lookup value) will be blank. I tried this:

=OR(VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,69,TRUE),VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,68,TRUE),VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,78,TRUE))

but it did not work. Any ideas?

Henry
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
try:
=VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,69,TRUE) & VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,68,TRUE) & VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,78,TRUE)
 
Upvote 0
The below array formula will pick up the maximum value across 3 (or more) columns,i.e the one that isn't blank...

Excel Workbook
ABCDEFG
1Lookup ValueCol1Col2Col3*Lookup*
211***11
32*25**225
43*17**317
54**31*431
65*42**542
7627***627
87**34*734
98**49*849
10979***979
1110*23**1023
Sheet1
 
Upvote 0
P45Cal,

That returned a value of "T000C", which is definately not in any of the colum number index's referenced. It should have come back with "15B" as out of the three cells it should have refenced to, 15B is the only value. The other two are blank.
 
Upvote 0
If the returned values are numbers:
Code:
=MAX(VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,{68,69,78},TRUE))
Enter with CTRL-SHIFT-ENTER!


If the returned values are text:
Code:
=LOOKUP(REPT("z",255),VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,{68,69,78},TRUE))
 
Upvote 0
P45Cal,

That returned a value of "T000C", which is definately not in any of the colum number index's referenced. It should have come back with "15B" as out of the three cells it should have refenced to, 15B is the only value. The other two are blank.
I tested this with a simpler setup and it worked, but it was very simple. Would it be better if you changed all instances of TRUE, to FALSE?
All this formula does is to concatenate three vlookups, and since you say 2 are blank, it shows up only the one (the other two being "").

Split the formula into it's three components in separate cells to help debug (though note that blanks will return zeroes, which you can circumvent by adding & "":
=VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,69,TRUE) & ""
=VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,68,TRUE) & ""
=VLOOKUP(E2,SIDPERS_ARNG!$A$2:$CZ$377,78,TRUE) & ""

and try changing TRUE to FALSE too.

ps. these columns are BQ,BP and BZ, is that right?
 
Last edited:
Upvote 0
Njimack,

This is the fomula I came out with, but it doesn't work?

{=MAX(IF($E$2:$E$405=E2,SIDPERS_ARNG!$CP$2:$CZ$377.""))}

As best I can tell it matches the formula you created, the only difference is that it has to look to a different tab to find the values to compare.

Can you see any error's I made on this?
 
Upvote 0
{=MAX(IF($A$2:$A$405=E2,SIDPERS_ARNG!$BP$2:$BZ$377.""))}

I hadn't realised that the 3 columns you're testing aren't continuous, so this approach may not work due to the layout of your data
 
Upvote 0
The colums it is refencing to CP,CQ, and CZ.
I doubt that.
<table valign="middle" colspan="5" style="font-family: Calibri; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-size: 11px; font-weight: normal; font-style: normal;" border="1" cellpadding="2" cellspacing="0"><colgroup><col width="28pt"><col width="60pt"><col width="60pt"><col width="60pt"><col width="60pt"></colgroup><tbody><tr style="background-color: rgb(250, 250, 250);"><td colspan="5" align="middle">Worksheet 'SIDPERS_ARNG'</td></tr><tr style="background-color: rgb(202, 202, 202);"><td>
</td><td align="middle">A</td><td align="middle">BP</td><td align="middle">BQ</td><td align="middle">BZ</td></tr><tr><td style="background-color: rgb(202, 202, 202);" align="middle">1</td><td align="right">1</td><td style="background-color: rgb(247, 150, 70);" align="right">68</td><td style="background-color: rgb(247, 150, 70);" align="right">69</td><td style="background-color: rgb(247, 150, 70);" align="right">78</td></tr></tbody></table>
<table valign="middle" colspan="2" style="table-layout: auto; color: rgb(0, 0, 0); background-color: rgb(255, 255, 255); font-family: Calibri; font-size: 10px;" border="3" cellpadding="2" cellspacing="0"><colgroup><col width="40pt"><col></colgroup><tbody><tr style="background-color: rgb(238, 170, 170);"><td>Cell</td><td>Formula</td></tr><tr><td>A1</td><td>=COLUMN()</td></tr><tr><td>BP1</td><td>=COLUMN()</td></tr><tr><td>BQ1</td><td>=COLUMN()</td></tr><tr><td>BZ1</td><td>=COLUMN()</td></tr></tbody></table><table style="font-family: Arial; font-size: 7pt;"><tbody><tr><td style="color: rgb(51, 51, 51);">Created with Tab2HTML (v2.4.1). ©Gerd alias Bamberg</td></tr></tbody></table>
 
Upvote 0

Forum statistics

Threads
1,224,574
Messages
6,179,633
Members
452,933
Latest member
patv

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