EXCEL PROBLEM

[gc1234]

Hi all,

I have 2 sheets, 1 sheet does a VLOOKUP in the 2 sheet and if found i need to reduce the total on that row by one? is this possible, its so i can track stock and know exactly what is or isnt in, by the way, I use the SKU code that lookups on on the first sheet.

many thxs

 

[gc1234]

Thanks Ken on the explaination, still get #n/a even though both cells are format number with 0 decimal places

number in cell b13 is 716770009715
and in sheet2!a1667 is 716770009715

:-(

 

[KenWright]

OK, you have me confused now. The formula you posted

=MATCH(TEXT(A1667,"0"),TEXT(Sheet1!B13,"0"),0)

doesn't really do anything, but if you have your original formula:-

=MATCH(TEXT(A1667,"0"),Sheet1!B1:B4000,0)

then the value you are looking up, ie whatever is in A1667 will be returned as TEXT by the TEXT function. this means that if you look it up in anything other than a series of TEXT values, you will not get a match. If the data in A1667 is already numeric, and the data in B1:B4000 is already numeric

(To test a value in A1 for this, in any other cell do =ISTEXT(A1) - False means it is a number)

then just use

=MATCH(A1667,Sheet1!B1:B4000,0)

This is looking for a number in a series of numbers.

If however, the data in B1:B4000 is text (use the test above that I gave you), then you will need to use the TEXT function that you had earlier, ie:-

=MATCH(TEXT(A1667,"0"),Sheet1!B1:B4000,0)

Happy to send you a sample file if you like illustrating both of these options.

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[gc1234]

That would be very helpful on the above email please

ps: all lookup cells are numeric

 

[gc1234]

I have just added this thanks to skip but would like to make it not the value of the index but the occurence of a match, below is what I added ken to your example sent... hope I'm clear,

=INDEX(Sheet1!$F$14:$F$30,H18,1)