Excel Testing

TrickyDicky101

stevemcol wrote: »

Index/Match are useful when the target data is to the left of the lookup value (Vlookup can only look right). It saves having to generate a ghost column.

I didn't ask why use INDEX/MATCH in preference to VLOOKUP, I asked why John424 used it to resolve the text vs numeric issue (which doesn't seem particularly relevant to me, so maybe I would have had an equally confused look as the tutor).

Incidentally, you can use VLOOKUP to look up left, although it does require use of a further embedded CHOOSE function:

http://vlookupweek.wordpress.com/2012/03/27/richard-schollar-vlookup-left/

stevemcol

TrickyDicky101 wrote: »

I didn't ask why use INDEX/MATCH in preference to VLOOKUP, I asked why John424 used it to resolve the text vs numeric issue (which doesn't seem particularly relevant to me, so maybe I would have had an equally confused look as the tutor).

Incidentally, you can use VLOOKUP to look up left, although it does require use of a further embedded CHOOSE function:

http://vlookupweek.wordpress.com/2012/03/27/richard-schollar-vlookup-left/

I didn't read it as a text versus numeric problem. Just the obvious limitation of vlookup sprang to mind. Yes I've used choose() thanks.

I actually thought John's post was a bit self important (sorry John, read it back and you might agree). I can't imagine an excel tutor would be 'gobsamcked or clueless' at the use of a common pair of functions, nor is it out of the ordinary for an employer to ask a candidate to substantiate a claim on their CV.

John424

The reason I used INDEX MATCH rather than VLOOKUP was when VLOOKUP doesn't work because your reference data or look up data has a text/number confusion, it's a pain either fiddling with either, multiplying it in by 1 etc etc in an attempt to resolve. With INDEX MATCH you can quickly adjust the formula i.e. INDEX(MATCH(A1,... or INDEX(MATCH(--A1,.. or INDEX(MATCH(A1&"",...., one of these 3 permutations will always work.

John424

Yes as above I understand now in 'new' Excel you can use VLOOKUP to go right to left which is an improvement. I still can't get my head around the 'new' Excel, needs more key strokes to achieve things although I do like the new SUMIFS function.

John424

Wyebird, I hope you passed, I realised I have hijacked this thread, sorry.

TrickyDicky101

John424 wrote: »

The reason I used INDEX MATCH rather than VLOOKUP was when VLOOKUP doesn't work because your reference data or look up data has a text/number confusion, it's a pain either fiddling with either, multiplying it in by 1 etc etc in an attempt to resolve. With INDEX MATCH you can quickly adjust the formula i.e. INDEX(MATCH(A1,... or INDEX(MATCH(--A1,.. or INDEX(MATCH(A1&"",...., one of these 3 permutations will always work.

=VLOOKUP(A1&"",...

=VLOOKUP(A1*1,...

INDEX/MATCH is what I usually use in preference to VLOOKUP (because of the left lookup issue and other efficiency reasons).

Anyway, I hope the OP was successful.

jackomdj

Never used choose, thank.

I do like sumifs, easily pleased me!