filling C3 with cell data from 1 of 3 other cells (if it exists)

[nzt101]

Hey guys,

at the moment i have a spreadsheet with this formula in a cell
(vlookupneg is vba so it looks to the left of the H column and works fine)

=IF(VLOOKUPneg(C3,'Batch'!H:H,-4,FALSE)<>"",VLOOKUPneg(C3,'Batch'!H:H,-4,FALSE),VLOOKUPneg(C3,'Batch'!H:H,-5,FALSE))
the above looks at a cell, matches C3 on sheet Batch, and pulls the data from -4 if it exists, or from -5 if theres nothing in -4

is there a way of adding another cell (E17) on the original sheet (where C3 is) so that if there is something in E17 it will be used instead of the two that are currently in use (-4 / -5) ?

thanks in advance!

 

[jasonb75]

Personally, I would use INDEX and MATCH instead of the VLOOKUPneg UDF. For it to function correctly, it would have to be volatile which means that it will be less efficient than the alternative.

With Office 365 / Excel 2021 I would suggest something like one of these.

=LET(m,MATCH(IF(E17<>"",E17,C3),'Batch'!H:H,0),d,INDEX('Batch'!D:D,m),c,INDEX('Batch'!C:C,m),IF(d<>"",d,IF(c<>"",c,"")))

=IF(E17<>"",E17,LET(m,MATCH(C3,'Batch'!H:H,0),d,INDEX('Batch'!D:D,m),c,INDEX('Batch'!C:C,m),IF(d<>"",d,IF(c<>"",c,""))))

Note that reading the last line of your question it is not clear if E17 should be used in place of the formula criteria in C3, or in place of the result of the existing formula. In absence of clarity I've provided both versions.

 

[nzt101]

Personally, I would use INDEX and MATCH instead of the VLOOKUPneg UDF. For it to function correctly, it would have to be volatile which means that it will be less efficient than the alternative.

With Office 365 / Excel 2021 I would suggest something like one of these.

=LET(m,MATCH(IF(E17<>"",E17,C3),'Batch'!H:H,0),d,INDEX('Batch'!D:D,m),c,INDEX('Batch'!C:C,m),IF(d<>"",d,IF(c<>"",c,"")))

=IF(E17<>"",E17,LET(m,MATCH(C3,'Batch'!H:H,0),d,INDEX('Batch'!D:D,m),c,INDEX('Batch'!C:C,m),IF(d<>"",d,IF(c<>"",c,""))))

Note that reading the last line of your question it is not clear if E17 should be used in place of the formula criteria in C3, or in place of the result of the existing formula. In absence of clarity I've provided both versions.

i used that bottom one and it worked perfectly,
yeah E17 is where i can put in a manual number to override the other two that were in -4 and -5 locations.

im almost at the point now where theres only a couple of vlookupneg's still in use
=VLOOKUPneg(C3,'Batch'!H:H,-6,FALSE) <- required cell is in column 2/B
and
=IFERROR(IFERROR(VLOOKUPneg(C3,Sheet2!H:H,-2,FALSE),VLOOKUPneg(TRIM(C3),Sheet2!H:H,-2,FALSE)),"Not Found") <- required cell is in column 6/F

are my last two lines, any chance you could show me the index/match fixes for those?

thanks very much for your help on the original Q

 

[jasonb75]

Do you need the formulas to work with excel 2007 (as you have it shown on your user profile)?

If not then you could use XLOOKUP, which you may find easier than INDEX and MATCH.

For the first formula in your post, the 2 alternatives would be

=IFERROR(INDEX('Batch'!B:B,MATCH(C3,'Batch'!H:H,0),"Not Found")

=XLOOKUP(C3,'Batch'!H:H,'Batch'!B:B,"Not Found")

For the second one, is there any reason for trying without trimming first? Is it common to have extra spaces in the lookup range, Sheet2!H:H ?
If not then you should simply be able to use the same formulas as above by changing the sheet name and result column used.

 

[nzt101]

Do you need the formulas to work with excel 2007 (as you have it shown on your user profile)?

If not then you could use XLOOKUP, which you may find easier than INDEX and MATCH.

For the first formula in your post, the 2 alternatives would be

=IFERROR(INDEX('Batch'!B:B,MATCH(C3,'Batch'!H:H,0),"Not Found")

=XLOOKUP(C3,'Batch'!H:H,'Batch'!B:B,"Not Found")

For the second one, is there any reason for trying without trimming first? Is it common to have extra spaces in the lookup range, Sheet2!H:H ?
If not then you should simply be able to use the same formulas as above by changing the sheet name and result column used.

this workbook isn't used on any computers with 07 so i'll use xlookup, which worked great, thank you!
unfortunately i dont have any control over how the data is exported and i was having issues, one of the other forum members gave me that line with trim in it and that fixed the issue i was having so i think i may need to leave trim in there. i couldn't see any spaces myself when going through the column but regardless trim fixed the issue haha

thanks for your help!

 

[jasonb75]

Having a quick look at your other thread, Alex was using trim to convert invalid numbers to valid numbers. With that in mind, this should do it in one step.

=XLOOKUP(TRIM(C3),'Sheet2'!H:H,'Sheet2'!F:F,"Not Found")

 

[nzt101]

Sorry for the late reply, i've added that one and used it in conjunction with a concat as well, awesome,
thank you!