Finding the next search value using vlookup or other formulas

[Fahim Al Mamun]

Dear,
I have an excel file where there have some data which will find from sheet 2 and the searching value is in sheet1 , but i have similar value in column i need when first value search it will show the first value when it search the next value it will show the next data,how to do it?

[AliGW]

Are you still using Excel 2019?

[Fahim Al Mamun]

yes , i am using office 2019 and office 365 platform

[AliGW]

Which? The answer will determine the solution offered.

[Fahim Al Mamun]

Madam i need the solution in office 365 platform.

[AliGW]

Then please update your forum profile to Excel 365. Thanks.

[HansDouwe]

Please try in B2 and copy down:

Please Login or Register  to view this content.

[Glenn Kennedy]

or, all in one cell (no copy paste... delete expected results first):

=LET(A,A2:A6,B,Sheet2!A2:A6,MAP(A,MAP(A,LAMBDA(z,COUNTIF(A2:z,z))),LAMBDA(x,y,INDEX(Sheet2!B:B,AGGREGATE(15,6,ROW(B)/(B=x),y)))))

Is there a simpler way? It feels like there should be, but I can't see it and have to go now.

[Fahim Al Mamun]

This is my actual file, i have multiple invoice for payment, but i need to match all this
invoice if the payment is done, if done it will match with my actual payment and differ
this 2 , then i will find the due balance.
Thanks in advance

[Fahim Al Mamun]

Glenn ,could you help for this file?

[AliGW]

Administrative Note:

It has been only one hour since you last posted. Please do not bump threads until 24 hours have passed, and then only once each day. We are all volunteers here who have lives beyond this forum, and it is the weekend, to boot, which is family time for many. While you may be awake and working, this is a world wide forum and others may be playing, sleeping or working and not have the time to look into your issues this minute. Patience is important here. If you have an urgent issue, you may wish to look at paying for help in the Commercial Services sub-forum or hiring a professional consultant in your area. Again, please do not bump threads more often than once in every 24 hours: if you get the help you need today, then great, but you really should not count on it. Thanks for your understanding and patience.

[windknife]

Try this in F2 and copied down.

Please Login or Register  to view this content.

[HansDouwe]

About your original question in Post 1:

This is an alternatve all in one go (no copy needed):

Please Login or Register  to view this content.

You suddenly start with a new question in post #9, without first responding to all your responses to your previous question in Post #1.
It feels like the input is being ignored and that is not nice.
Does our answers your original question from Post #1? If so, it would be nice to receive a thank you for this.
You can also consider adding reputation to all helpers you think they deserve.
In that case please click * Add Reputation left below their answers.

Then start with a new question.

[HansDouwe]

Everything in one go is even easier without OFFSET:

Please try

Please Login or Register  to view this content.

[AliGW]

I didn't think that LAMBDA or MAP were available in Excel 2019 - have I got that wrong?

EDIT: Forget that - the OP said he's using 365, but still hasn't updated his profile - there's a surprise.

[Fahim Al Mamun]

Hi windknife,
thanks for your contribution,
in my file you see that i have multiple similar data in my invoice list in Order sheet, it will search the data from Invoice sheet, but i have to find the similar data from the invoice sheet , but its not showing the similar data in right position, how to fix it?

[HansDouwe]

About your question in Post #9:

Please empty the whole column from F2 and try in F2:

Please Login or Register  to view this content.

This formula returns exact the same results as the formula of Windknife in F12.

The results match your requirements of Post #1.

[windknife]

Could you upload a sample file with wrong formula?

[Fahim Al Mamun]

Thanks for the reply,
Actually I need to match the same value against my invoice number, but i the value
sometimes similar and sometimes very near to the exact value, i have no other clue to find
the actual value against the invoice number, in my file i have marked the actual output
of my requirements.

[Glenn Kennedy]

In your latest file the yellow cells are all EXACT matches with the invoiced amount. Is that REALLY true? I ask the question because you said: "and sometimes very near to the exact value".

If they can be different, can they be different ABOVE and BELOW the exact value?

Please upload a SMALL sample file (20 rows MAXIMUM) that matches EXACTLY your REAL data.

[windknife]

How about this in F2?

Please Login or Register  to view this content.

[HansDouwe]

In case of an exact amount please try in F2 for the whole column:

Please Login or Register  to view this content.

[Fahim Al Mamun]

Thank you very much Hans!
Its working very nice