 You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Thanks for your help on last weeks question, I've become pretty familiar with vlookup, index and match but I'm now stuck on something I thought would be easy.
Find Value A which is in one of 2 columns (it can be in either but not both so no conflicts), return the value of the cell 2 cells to the right of the A.
Thanks
Are you outsourcing your job to STW? 😀
you can do that with 2 vlookups and iferror(firstvlookup, secondlvookup)
use your MATCH and INDEX skillz with ISERROR and IF to parse both columns
EDIT: the purist beat me to it
Not sure if there's a neater solution but nested if statements aren't too hard to manage over 2 columns. You can use ISNUMBER and SEARCH to check a cell for specific text.
e.g.
IF(ISNUMBER(SEARCH ("A",A2)),C2, IF(ISNUMBER(SEARCH ("A",B2)),D2,""))
Checks A2 for "A", returns C2 if it finds it, if not it checks B2 and returns D2, or leaves blank if it's in neither.
You can also use OFFSET to return relative positions.
Are you outsourcing your job to STW?
I'm an industrial designer who has bitten off more than he can chew!
ps my solution is suitable for ordered data, ie. you know which row you're looking in and just need to return the correct cell depending on whether the value is in column a or b. Otherwise if with lookup as above.
My explanation was slightly wrong as there are about 10 columns the unique value could be in (and any row). Can you use more than 2 vlookups?
You can but IFERROR only works with 2 values, so you'd need to use nested IFs:
IF ( ISERROR (lookup1), if (iserror(lookup 2), if (iserrror(lookup 3), lookup4, lookup3), lookup2), lookup1) or something like that. I'd suggest trying it with 2 or 3 levels first then expand once you're comfortable.
You might be able to wangle it using an array formula too but that'd be 1) ninja level excel and 2) almost impossible for anyone else to understand
Are the rest of the columns blanks?
=IF(ISNUMBER(MATCH(value_A,firstcolumn,0)),INDEX(thirdcolumn,MATCH(value_A,firstcolumn,0)),IF(ISNUMBER(MATCH(value_A,secondcolumn,0)),INDEX(fourthcolumn,MATCH(value_A,secondcolumn,0)),"not found"))
Have PM'd you, OP
I tried your suggestion reggie but couldnt get rid of the errors.
I thought I'd done it with this but, it returns the value 2 columns to the left of the first lookup column regardless of what lookup column contained H15.
=IFERROR(VLOOKUP(H15,' Equipment & Graphics'!$F$8:$H$67,3),IFERROR(VLOOKUP(H15,' Equipment & Graphics'!$X$8:$AA$67,3),IFERROR(VLOOKUP(H15,' Equipment & Graphics'!AB15:AD74,3),"SS")))
It's not that important because the data is not too dynamic.
Thanks all
Sorted it, I'd missed out the false statement in the vlookups
Was just gonna point that out - Excel does some arbitary stuff if your leave that off. Also make sure the cell references are all absolute (ie. $AB$15:$AD$74) if you're going to copy & paste that.