If string match cell content in a range1, then display value of Range2 that belongs matched cell.

[David gonzalez]

Hello all !!

yes, i lil confused..I am having trouble with combining/match display if condition of dataset in excel, which I hope you could help me with!


If E37 match any cell in range1 DE35:EN35, Then display content from range2 (DE37:EN37)
that belongs to matched cell in range1

Let say I Input #01 in E37, Then in Range1 the #01 is located in Cell DE35,
So i need to display the cell that is associated to DE35 which is 2 row bellow (DE37)
In this example result that have to display is : 210

Description :

String -> E37
is where input 1 Alphanumeric Value 'between' 01 to 36

Range1 -> DE35:EN35
hold 36 Alphanumeric Value from 01 to 36

Range2 -> DE37:EN37
keep 3 digits between 000 to 999 that change time to time

Note :
Range1+Range2 are associated BY Row by column
(DE35 with DE37), (DF35 with DF37), (DG35 with DG37), (DH35 with DH37), etc, etc

https://dl.dropboxusercontent.com/u/70638562/RANGE1.png

 

[Peter_SSs]

There could be some issues about whether all values are text or formatted numeric, but for a start, try this.

=HLOOKUP(E37,$DE$35:$EN$37,3)

 

[David gonzalez]

Hi Mr Peter_SSs,

the formula is fine, but i don't know what to do next, due that when Input new values on E37, The whole row E37, scroll 1 row down (move to row 38) Then the E37 is ready for next Input to do the same (scroll 1 row down.. etc,etc)
The Issue is that is displaying same result over and over (while updating)

I think i have to check carefully, because i might did something wrong..

Thank you !!


Best regards

David

 

[David gonzalez]

Hi Mr Peter_SSs

How to hook up the 2 ranges separated,
For example the formula is : HLOOKUP(E37,$DE$35:$EN$37,3)
so how to put together in the formula the Ranges EN35:DE35 and EN37:DE37 ?


Thank you !

David

 

[David gonzalez]

Hi Mr Peter_SSs,

I'm sorry for previous dumb question... i've gooogle it..
when i asked, i was in shock because of the result..


Thank you,

David

 

[Peter_SSs]

Glad you have it sorted.

 

[David gonzalez]

Hi Mr Peter_SSs,

I fix the issue of same result over and over.. But now i have a new Issue, I'm getting #N/A error
might you know some tip to highlight me where to start?.. i vere worked with HLOOKUP..

Both area are formatted as Numbers, don't know if this is the issue that you mention on post #2


[img]https://dl.dropboxusercontent.com/u/70638562/Image 5.png[/img]

Thank you

David

 

[Peter_SSs]

[img]https://dl.dropboxusercontent.com/u/70638562/Image 5.png[/img]

This hyperlink doesn't work for me. In any case I can't copy from an image so it would be better if you posted some sample data directly in your post here. My signature block below has suggestions for how to post small screen shots of data that can be copied. It also suggests where to test the methods. I prefer Excel jeanie.